Consolidation Logic for multiple Rows into Single

[prawal1990]

Hi All,

I need your help to create a workflow in Alteryx . I am stuck while applying consolidation logic . I have two input files one is source and another is target

Expected Result

Consolidation  logic is applied based on the Product_Code, Contract_Number,Contract_start_date,Contract_end_date

Can you help me to generate one Output file which consolidate this source and Target columns along with the Match & Mismatch for all the above columns.
Another type of source data is when product code is different for same Contract Number then no consolidation will be happen. so we need to handle those as well 

Source

Contract_Number	Contract_Item_Number	Product_Code	Annualized_Value	Contract_start_date	Contract_end_date
30515758	500	2000014273	5209.09	2026-06-08	2027-06-07
30515758	740	2000014273	4914.23	2025-06-08	2026-06-07
30515758	500	2000014273	4592.74	2024-06-08	2025-06-07

Target

Contract_Number

Contract_Item_Number

ProductCode

AmountPrice

AmountPrice2

Amount_Price_3

Amount_Price_4

Amount_Price_5

Amount_Price_6

Amount_Price_7

EndDate

AmountAllocatedFinal

Year2AmountAllocatedFinal

Year3AmountAllocatedFinal

Year4AmountAllocatedFinal

Year5AmountAllocatedFinal

StartDate

Year1Amount

Year1EndDate

Year1StartDate

Year2Amount

Year2EndDate

Year2StartDate

Year3Amount

Year3EndDate

Year3StartDate

30515758

500

2000014273

4592.74

4914.23

5209.09

6/7/2027

4592.74

4914.23

5209.09

6/8/2024

4592.74

6/7/2025

6/8/2024

4914.23

6/7/2026

6/8/2025

5209.09

6/7/2027

6/8/2026

[abacon]

@prawal1990 Here is a workflow that gets you most of the way there. I added in the check if product number is different for that same contract numbers. The only issue is the Contract_Item_Number is different, how does that effect the desired output?

Bacon

[prawal1990]

Hi Abacon, Thanks for the quick response. It will not impact our desired output based on the First Start and End Date it will pick Contract_Item_Number. 

Also, I have two input files here , You have shared with Source only. I need to compare Source VS Target via Formula Tool.

PFA the workflow I have created for the reference. 

[abacon]

I misread that, apologies. Could you package that workflow up so the input files can come with it? What you uploaded doesn't allow me to see your input files. Additionally, could you post your desired output? The desired end result is not very clear from your post.

Bacon

[prawal1990]

I can't upload my Source & Target Data here but you can put above mentioned sample Source & target data in two excel file as a input files and proceed further. I hope it will make sense. Also accidently I mentioned duplicate Contract_Item_Number as 500 in Source , it should always be unique we can consider it as 600 suppose. 

Sorry for the inconvenience

Contract_Number	Contract_Item_Number	Product_Code	Annualized_Value	Contract_start_date	Contract_end_date
30515758	600	2000014273	5209.09	2026-06-08	2027-06-07
30515758	740	2000014273	4914.23	2025-06-08	2026-06-07
30515758	500	2000014273	4592.74	2024-06-08	2025-06-07

[abacon]

Thank you! What is the output you are trying to see? It is not clear what you are trying to do with these 2 data sources?

[abacon]

Per offline discussion, this should get you what you want.

[abacon]

@prawal1990 

[prawal1990]

@abacon : PFA the sample source data. It's not working with input excel .However working fine with  sample input data. Kindly check it.

[abacon · Accepted solution]

Updated File

@prawal1990 Made a slight adjustment, got it to work.

Please mark this as the solution so others may find it faster.