3 Way Data Reconciliation

[skotian1289]

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

[paulfound]

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.

[skotian1289]

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

[skotian1289]

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

[ChrisTX]

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

[skotian1289]

Hey Chris,

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

[ChrisTX]

Here are a few related posts for processing multiple rows, multiple fields:

Dynamic formula calculations for multiple fields and multiple rows

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Multi-field-Calculations-Multi-row-Cal...

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+-

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Multi-Row-Multi-Field-formula-field/m-...

Fill values down multiple columns dynamically

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Developing-a-Macro-for-filling-down-va...

[skotian1289]

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

[atcodedog05]

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.

[skotian1289]

@atcodedog05 

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