3 Way Data Reconciliation
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hello,
I am trying to reconcile data between 3 different files at once and I was able to get all 3 column values transposed together and assign Group column numbers but I am not able to reconcile them together. Is there an alternate way or is it that m doing it wrong, please help ? Please see below sample for details.
I am using Multi row formula and formula is as per below which I feel is not correct, do I need to creat multiple formulas for 3 way recon.
if [Group Column Number] = 3 then if [Value] = [Row-1:Value] then "True" else "False" endif else "-" endif
Expected results as below:
File Name Value Group Column Number Results
A Lion 1 Value Matches with File C but not with File B
B Whale 2 Value does not match with File A and File C
C Lion 3 Value Matches with File A but not with File B
A Tiger 1 Value Matches with File B and C
B Tiger 2 Value Matches with File A and C
C Tiger 3 Value Matches with File A and B
Thanks
Solved! Go to Solution.
- Labels:
- Data Investigation
- Python
- Regex
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @skotian1289
Think this is what your after, I used the multijoin tool by position just add the files together then did a formula across them.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi Paul,
Thanks for the response the problem with below solution is I have around 1000s of columns in 3 files with different header names hence I will have to create formula for each off them hence I transposed the data to segregate them as Name and values. Your solution will work only if my column header is constant and I can apply formula using those headers. Correct me if m wrong.
Thanks
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Paul - It was my bad I did not give you proper sample. See below
File Name Value Group Column Number Results
A Lion 1 Value Matches with File C but not with File B
B Whale 2 Value does not match with File A and File C
C Lion 3 Value Matches with File A but not with File B
A2 Tiger 1 Value Matches with File B and C
B2 Tiger 2 Value Matches with File A and C
C2 Tiger 3 Value Matches with File A and B
A3 Tiger 1 Value Matches with File B and C
B3 Tiger 2 Value Matches with File A and C
C3 Tiger 3 Value Matches with File A and B
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Can you provide an Excel example of your actual input data? Starting from the processed data you provided may not be the best option.
You don't have to provide 1000 columns, but providing a representative sample of the initial data, complete with the different column names, may help.
Chris
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Here are a few related posts for processing multiple rows, multiple fields:
Dynamic formula calculations for multiple fields and multiple rows
Multi Row - Multi Field
https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Multi-Row-Multi-Field/m-p/50403#M19125
Multi Row / Multi Field formula: field+-
Fill values down multiple columns dynamically
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hello,
I am sorry but I am pretty new to Alteryx and 3 way recon is little difficult for me to perform on alteryx than excel. Is it possible for anyone to help me with a sample workflow for the same, that would be very helpful for me to understand.
Thanks
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @skotian1289 ,
Can provide some sample data as in Actual files and expected output we can look into it.
I its a task for Union Tool and Multi-Row formula tool.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Sure here is the attached, I have provided sample of 3 input files and also a sample of a processed file i.e till what point my current tool is able to process the data. Hope this helps. Thanks
