Data Prep - how to build working data from multi inputs

Hi everyone,

I am trying to figure out how to create a "working " data set that is created from monthly excel inputs.

The idea is to combine the differences between an “incoming” dataset and a “created” dataset. I am sure this must be fairly standard…

Receive Data set Month 1.
• Data Cleanse
• Write back to a new output file “CUMULATIVE” Excel or CSV format. Local file to begin
• Continue analysis

Receive Data set Month 2.
• Data Cleanse new input file
• If column “New Input File” Spend Description1 and Description2 are equivalent to column “CUMULATIVE” data sets them create new column (Timestamped1) with the difference in Spend.
• If there is no equivalent Description1 and Description2 in Month 2 write the new spend data to the new column (Timestamped1)
• If column Travel Description1 and Description2 are equivalent in both data sets them create new column (Timestamped1) with the difference in Travel.
• Write back to “CUMULATIVE”
• Continue analysis

Receive Data set Month 3.
• Data Cleanse new input file
• If column “New Input File” Spend (Timestamped1) Description1 and Description2 are equivalent in both data sets them create new column (Timestamped2) with the difference in Spend.
• If column Travel (Timestamped1) Description1 and Description2 are equivalentto column “CUMULATIVE” data them create new column (Timestamped2) with the difference in Travel.
• If there is no equivalent Description1 and Description2 in Month 2 write the new spend data to the new column (Timestamped2)
• Write back to “CUMULATIVE”
• Continue analysis

etc etc for each months input

screenshot of process

screenshot of example data:

Join doesnt seem to be the way forward due to the need to do the checks and column creation/timestamps
If someone could point towards the kind of nodes that I would look at to accomplish this in Knime rather than perhaps doing it as excel manual steps each time i receive an input i would be grateful.

Thanks
Mark

What about joining first then do your checks, filter, create your columns…
br

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.