Alteryx Designer Discussions

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

Iterative Data Comparison

Kb7980
6 - Meteoroid

HI

I am looking to compare data between 2 files - Pre and Post. 

1.Col A to Col D in both files are columns which form key to compare data 

2. Col E and Col F are being compared 

3. When data matches , i am looking to populate "Match" in a new column for Col E and Col F

4.When data dsnt match, i am looking to populate "MisMatch"

5. When there is new key in Post, i am looking to populate N/A

6. Keys in both Pre and Post can be repetitive.

 

The bigger problem in this situation is that instead of 2 target columns that I need to compare, I actually have 100 columns that need to be compared. With little experience that I have, it looks like a batch macro might have to be used here but I have no idea on how to implement this. Any help is appreciated

16357866610476559983347706612739.jpg

 

 

18 REPLIES 18
mceleavey
16 - Nebula

Hi @Kb7980 ,

 

can you post the data so we can help? A photograph doesn't help us at all.

 

M.

Kb7980
6 - Meteoroid

Attaching the files.

AZuc
Alteryx
Alteryx

@Kb7980 , see if it helps.

 

 

André Zuccatti

Sales Engineer - LATAM -
Alteryx, Inc.


Kb7980
6 - Meteoroid

Hi Azuc /all

Sorry , think I did not explain my issue well. I combined post and output files earlier but have segregated them now to be more clear

Have attached 3 files here

PRE

POST

OUTPUT

 

I am looking to compare data between 2 files - Pre and Post and populate the results as in Output file (attached here)

1.Col A to Col D in both files are columns which form key to compare data 

2. Col E and Col F are being compared 

3. When data matches , i am looking to populate "Match" in a new column for Col E and Col F

4.When data dsnt match, i am looking to populate "MisMatch"

5. When there is new key in Post, i am looking to populate N/A

6. Keys in both Pre and Post can be repetitive.

7. Results have to be populated for all rows in Post sheet

The bigger problem in this situation is that instead of 2 target columns that I need to compare, I actually have 100 columns that need to be compared. With little experience that I have, it looks like a batch macro might have to be used here but I have no idea on how to implement this. Any help is appreciated

AZuc
Alteryx
Alteryx

@Kb7980 

 

I see only 3 differences between your output and the result of the workflow

1 - I've generated columns for Pre and Post Values plus Status. Your output show only Post Value. In this case you could ignore the treatment I've made to Pre Values and remove it as an input to Union Tool.

2 - The order of records respect the order of the Post file. If its the case, adding a record ID and sorting in the end may solve it.

3 - Column names are slightly different. A Dynamic Rename could help here

 

Please confirm you need these 3 changes?

 

Is there anything else I've missed?

André Zuccatti

Sales Engineer - LATAM -
Alteryx, Inc.


Kb7980
6 - Meteoroid

Hi Andre

I see only 3 differences between your output and the result of the workflow

1 - I've generated columns for Pre and Post Values plus Status. Your output show only Post Value. In this case you could ignore the treatment I've made to Pre Values and remove it as an input to Union Tool.

Kb - Yes, only want to show Post value.

2 - The order of records respect the order of the Post file. If its the case, adding a record ID and sorting in the end may solve it.

Kb - Yes that is the case.

3 - Column names are slightly different. A Dynamic Rename could help here

Kb- Agreed to this also.

 

Kb- I did have a look at your workflow and the only other thing that want to confirm is that this can be extended to a scenario where instead of 2 columns to be comapred, I actually have 100. Think your workflow will be able to takr care of that with small changes. Hope that is correct understanding.

Thanks regards

Kb

AZuc
Alteryx
Alteryx

@Kb7980 

 

Yes, the WF was built to work with as many columns you have, only making sure that ColA-D exists and that will be a column ending with Status for each extra column you have. Let me send a V2 removing the original Status Columns and recalculating them.

André Zuccatti

Sales Engineer - LATAM -
Alteryx, Inc.


AZuc
Alteryx
Alteryx

@Kb7980 there it is

André Zuccatti

Sales Engineer - LATAM -
Alteryx, Inc.


Kb7980
6 - Meteoroid

Thanks Andre. I have been testing this on the data that I have and it seems all ok except for when the Col A to Col D combinations are being repeated either in Pre or Post files.

I have attached files with such records added and for these the workflow kind of stutters. Appreciate your help but did not realize there would be such data that can cause issues.

There is possibility of such data combinations getting repeated in both pre and post files.

 

Labels