Compare and combine files

[Harinder]

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

[adamweaver39]

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

[Harinder]

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]

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!

[Harinder]

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 · Accepted solution]

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