Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

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

ssripat3
8 - Asteroid

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

 

PBSPrefixBaseSuffixCost
P1-B1-S1P1B1S1150
P2-B2-S2P2B2S2250
P3-B3-S3P3B3S3350
P4-B4-S4P4B4S4550
P6-B6-S6P6B6S6650

 

Output-BOM-5-25-2022

 

PBSPrefixBaseSuffixCost
P1-B1-S1P1B1S1100
P2-B2-S2P2B2S2200
P3-B3-S3P3B3S3300
P5-B5-S5P5B5S5400
P6-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.

 

PBSPrefixBaseSuffixActionChange
P1-B1-S1P1B1S1Revised50
P2-B2-S2P2B2S2Revised50
P3-B3-S3P3B3S3Revised50
P4-B4-S4P4B4S4Add550
P6-B6-S6P6B6S6No Change0
6 REPLIES 6
DataNath
17 - Castor

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

 

DataNath_0-1653582718584.png

 

ssripat3
8 - Asteroid

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

ssripat3
8 - Asteroid

@DataNath 

 

I just need one more advice. If I want to compare more than one column with values from another column. What would be the best way of doing it?

 

PBSPrefixBaseSuffixCostIndicatorDate
P1-B1-S1P1B1S1150Y08-02-2021
P2-B2-S2P2B2S2250N09-02-2021
P3-B3-S3P3B3S3350N10-02-2021
P4-B4-S4P4B4S4550Y11-02-2021
P6-B6-S6P6B6S6650Y12-02-2021

 

 

PBSPrefixBaseSuffixCostIndicatorDate
P1-B1-S1P1B1S1100Y08-02-2021
P2-B2-S2P2B2S2200Y09-02-2021
P3-B3-S3P3B3S3300N10-03-2021
P5-B5-S5P5B5S5400Y11-02-2021
P6-B6-S6P6B6S6650N12-03-2021

 

I would like to now compare the values "Cost", "Indicator" and "Date" across the two tables. Let us say if any field changes and differs from the first table. Then we say the Action is "Revised". But in this instance we have to change it to "Revised" even when one single column doesn't match the data from the other file.

 

I think Multi-Row formula is the best way to approach this, but I am lost of writing a formula using that tool.

 

Please help me if you can

DataNath
17 - Castor

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:

 

DataNath_0-1656353488528.png

 

ssripat3
8 - Asteroid

@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
17 - Castor

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

Labels