In case you missed the announcement: Alteryx One is here, and so is the Spring Release! Learn more about these new and exciting releases here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Compare and combine files

Harinder
7 - Meteor

Hello everyone,

 

I have multiple files in yxdb format, and I would like to combine them into a single yxdb file without any duplicate records while also maintaining the history. Please see attached sample excel file for required outputs. Appreciate your suggestions.

Thanks

 

 

 

 

5 REPLIES 5
adamweaver39
9 - Comet

Hi @Harinder -- please see attached workflow and let me know if this fulfills your goal.SampleEx - 4.9.24.jpg

Harinder
7 - Meteor

Thanks, I want to compare the records date wise if its a new record then assign the action as "new" and if ID +category combination already exist then append the row with Action type "update" for that combination (group by ID and category).

 

adamweaver39
9 - Comet

Hi @Harinder, gotcha. See updated workflow attached and below. I found this fairly straightforward to accomplish with a multi-row formula, grouped by id + category. You could also include property as an additional grouping consideration. Hopefully that does the trick!

SampleEx - 4.9.24.jpg

Harinder
7 - Meteor

Thank you!

 

Much appreciated.

 

Actually, I would like the output to include null/blank values as well. If either of the properties is updated, then the action should be "update". If it is a new record, then the action is "insert", otherwise, the action is "same".

 

For example, for the 200-MS400 record:

On 12/3:

p1 = 0, action = insert
p2 = [null], action = insert
p3 = blank, action = insert


For the 200-NN401 record:

p1 = blank, action = insert
p2 = blank, action = insert
p3 = 500, action = insert

 


On 13/3:

For the 200-MS400 record:

p1 = 10, action = update
p2 = [null], action = same
p3 = blank, action = same


For the 300-MS400 record:

p1 = 100, action = insert
p2 = [null], action = insert
p3 = [null], action = insert


On 14/3:

For the 200-MS400 record:

p3 = 45, action = update
p2 = [null], action = same
p1 = [null], action = same


For the 300-NN401 record:

p3 = 145, action = update
p2 = [null], action = same
p1 = [null], action = same


Thank you in advance.

Best,

adamweaver39
9 - Comet

Hey @Harinder -- I think this formula should do the trick if I understand it correctly.

SampleEx - 4.10.24.jpg

Labels
Top Solution Authors