I have a dataset that runs daily through a workflow that gives me an output that we will call Output-BOM-TIMESTAMP. I need to check and compare data amongst both the files where the new timestamp file will be considered the latest. 1) I would like to compare both the files considering "Output-BOM-5-26-2022" to be the latest. We will have to remove any row or record that is not present in the new file considering P-B-S column to always be unique. Output-BOM-5-26-2022 PBSPrefixBaseSuffixCostP1-B1-S1P1B1S1150P2-B2-S2P2B2S2250P3-B3-S3P3B3S3350P4-B4-S4P4B4S4550P6-B6-S6P6B6S6650 Output-BOM-5-25-2022 PBSPrefixBaseSuffixCostP1-B1-S1P1B1S1100P2-B2-S2P2B2S2200P3-B3-S3P3B3S3300P5-B5-S5P5B5S5400P6-B6-S6P6B6S6650 2) I would like to now process through the data and create two new columns in the Output file. "Action" and "Change". In Action, we have 3 categories. "Add", "Revised", "No Change" depending on the change in Cost values from one dataset to another. In Change, I would like to calculate the difference in Cost from one dataset to another. I am adding the expected output down below and if anyone could help me with this workflow. It would be great. PBSPrefixBaseSuffixActionChangeP1-B1-S1P1B1S1Revised50P2-B2-S2P2B2S2Revised50P3-B3-S3P3B3S3Revised50P4-B4-S4P4B4S4Add550P6-B6-S6P6B6S6No Change0

Compare two files with same headers and identify the changes and calculate cost difference

I have a dataset that runs daily through a workflow that gives me an output that we will call Output-BOM-TIMESTAMP. I need to check and compare data amongst both the files where the new timestamp file will be considered the latest.

1) I would like to compare both the files considering "Output-BOM-5-26-2022" to be the latest. We will have to remove any row or record that is not present in the new file considering P-B-S column to always be unique.

Output-BOM-5-26-2022

PBS	Prefix	Base	Suffix	Cost
P1-B1-S1	P1	B1	S1	150
P2-B2-S2	P2	B2	S2	250
P3-B3-S3	P3	B3	S3	350
P4-B4-S4	P4	B4	S4	550
P6-B6-S6	P6	B6	S6	650

Output-BOM-5-25-2022

PBS	Prefix	Base	Suffix	Cost
P1-B1-S1	P1	B1	S1	100
P2-B2-S2	P2	B2	S2	200
P3-B3-S3	P3	B3	S3	300
P5-B5-S5	P5	B5	S5	400
P6-B6-S6	P6	B6	S6	650

2) I would like to now process through the data and create two new columns in the Output file. "Action" and "Change". In Action, we have 3 categories. "Add", "Revised", "No Change" depending on the change in Cost values from one dataset to another.

In Change, I would like to calculate the difference in Cost from one dataset to another.

I am adding the expected output down below and if anyone could help me with this workflow. It would be great.

PBS	Prefix	Base	Suffix	Action	Change
P1-B1-S1	P1	B1	S1	Revised	50
P2-B2-S2	P2	B2	S2	Revised	50
P3-B3-S3	P3	B3	S3	Revised	50
P4-B4-S4	P4	B4	S4	Add	550
P6-B6-S6	P6	B6	S6	No Change	0

Workflow

Datasets

Output

Join

Accepted answers

DataNath

Hi @ssripat3, how does this look? Works for your example but let me know if there are any issues you come across when testing!

Compare previous to most recent file.yxmd

All comments

DataNath

Hi @ssripat3, how does this look? Works for your example but let me know if there are any issues you come across when testing!

Compare previous to most recent file.yxmd

ssripat3

@DataNath Thank you for helping me out. It works for other sample data I tested on. Thank you

DataNath

How does this look @ssripat3? To start with, P5-B5-S5 is missing because I joined on PBS for the comparison - is this right or should I just join on position, regardless of whether the PBS matches? To me it just looked like this was the only/obvious unique identifier. Also, I've only left the new table in the view to finish with. You can show the original table by its side by selecting them again in the join configuration:

Compare 2 tables.yxmd

ssripat3

@DataNath Thank you for the help and pardon my dumb brain. I was overthinking it and literally forgot about OR condition and was thinking is a bit complex way of using Multi-Row formula

DataNath

No problem @ssripat3 - happy to help! Don’t beat yourself up, happens to everyone from time to time!

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335