Regarding Consolidation Logic handling in Workflow

[prawal1990]

I have two input files one is source and another is target

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__c

AmountPrice__c

AmountPrice2__c

Amount_Price_3__c

Amount_Price_4__c

Amount_Price_5__c

Amount_Price_6__c

Amount_Price_7__c

EndDate__c

AmountAllocatedFinal__c

Year2AmountAllocatedFinal__c

Year3AmountAllocatedFinal__c

Year4AmountAllocatedFinal__c

Year5AmountAllocatedFinal__c

StartDate__c

Year1Amount__c

Year1EndDate__c

Year1StartDate__c

Year2Amount__c

Year2EndDate__c

Year2StartDate__c

Year3Amount__c

Year3EndDate__c

Year3StartDate__c

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

Expected :

1. Above Target line is consolidate from source input based on the Product_Code, Contract_Number,Contract_start_date,Contract_end_date

2. AmountPrice__c -> Mapped from Annualized_Value of First Contract_start_date & Contract_end_date. Similarly applied to other columns like AmountAllocatedFinal__c and Date columns too

Can someone help me to generate one Output file which consolidate this source and Target columns along with the Match & Mismatch for all the above columns.

Note: Another type of source data is when product code is different for Contract Number then no consolidation will be happen.  SO we need to handle those as well.

[Vinod28]

Hi @prawal1990 

As part of our effort to streamline and validate contract data, please find the attached source and target dataset files for your kind reference. This solution ensures accurate year-by-year value mapping and highlights discrepancies at the field level for clear auditability.

---

Objective: To consolidate multi-year contract entries from the Source dataset and validate them against structured fields in the Target dataset (including amounts and start/end dates), while flagging matches and mismatches.

---

Step-by-Step Workflow Breakdown

Step 1: Input Data

• Loaded both source_dataset.csv and target_dataset.csv using two Input Data tools in Alteryx.

Step 2: Data Cleansing

• Used Data Cleansing and DateTime Parse tools to normalize date fields like Contract_start_date and Contract_end_date.

• Removed malformed entries (e.g., line breaks and nulls).

Step 3: Join Logic

• Performed an inner join on:

  • Contract_Number

  • Contract_Item_Number

  • Product_Code (Source) → 'Pr_ProductCoPr_ProductCode__c (Target)

• This ensured precise row-level alignment between contract entries.

Step 4: Field Mapping and Comparison

• Applied a Formula tool to compare fields and flag results:

  plaintext

  IF [Annualized_Value] = [AmountPrice__c] THEN "Match" ELSE "Mismatch" ENDIF
  IF [Contract_start_date] = [Year1StartDate__c] THEN "Match" ELSE "Mismatch" ENDIF
  IF [Contract_end_date] = [Year1EndDate__c] THEN "Match" ELSE "Mismatch" ENDIF
  

• Similar logic extended to Year 2 and Year 3 where applicable (AmountPrice2__c, Year2StartDate__c, etc.).

Step 5: Unmatched Records

• Captured source records without a corresponding target entry using the right output of the Join tool.

• These were flagged as "No Match Found" for clarity.

Step 6: Output Generation

• Generated two final outputs:

  1. Matched Records – including field-level match/mismatch flags.

  2. Unmatched Records – source entries with no corresponding target match.

[Vinod28]

Hi @prawal1990 

Thank you for your detailed explanation. I’ve reviewed the input data structure and your intended target mapping, and your logic is valid. Based on the requirement to consolidate multiple rows into a single record per Contract_Number + Product_Code, and to sequence the Annualized_Value fields by contract dates, here is a technically structured approach using Alteryx.

Step 1: Input and Schema Normalization

• Tools: Input Data, Select Tool

• Load both Source and Target datasets.

• Rename ProductCode__c → Product_Code in the Target.

• Parse Contract_start_date and Contract_end_date as Date types.

---

Step 2: Key-Based Join

• Tool: Join Tool

• Join Source and Target on:

  • Contract_Number

  • Product_Code

• Retain only the J (joined) output stream for downstream logic.

• Fields to retain: Contract_Item_Number, Annualized_Value, Contract_start_date, Contract_end_date.

---

Step 3: Chronological Sort for Sequencing

• Tool: Sort Tool

• Primary Sort: Contract_start_date ASC

• Secondary Sort: Contract_end_date ASC

• This defines the correct sequence for price field assignment.

---

Step 4: Partitioned Indexing

• Tool: Multi-Row Formula Tool or Tile Tool

• Group By: Contract_Number, Product_Code

• Assign a numeric RowIndex starting at 1 within each group.

• Output: field RowIndex drives dynamic column naming.

---

Step 5: Dynamic Column Name Generation

• Tool: Formula Tool

• Create field MappedFieldName using conditional logic:

IIF([RowIndex] = 1, "AmountPrice_c", "AmountPrice" + ToString([RowIndex]) + "_c")

Step 6: Row-to-Column Transformation

• Tool: Cross Tab Tool

• Group By: Contract_Number, Product_Code

• Column Headers: MappedFieldName

• Values: Annualized_Value

• Aggregation: First (assuming one value per field per key)

---

Step 7: Optional Metadata Reintegration

• Tool: Join Tool + Select Tool

• If additional Target fields are required (e.g., Contract_Item_Number), rejoin using Contract_Number + Product_Code.

• Use Select Tool to reorder columns or eliminate redundant fields.

---

Step 8: Output and QA

• Tools: Output Data Tool, Join Tool, Formula Tool, Filter Tool

• Write the final output to .csv, .xlsx, or .yxdb.

• For validation:

  • Join with the expected Target dataset.

  • Use field-level comparisons (e.g., IF [AmountPrice_c] ≠ [Target.AmountPrice_c]) to detect mismatches.

  • Use Filter Tool to isolate discrepancies.

Please check the above steps and let me know how it works. Thank you.

[Vinod28]

Hi Prabal,

As discussed, to map fields like AmountPrice2, AmountPrice3, YearStartDate1etc. Here are two options:

---

Option 1: Simple (for fixed rows)
If you only expect a few rows per contract (e.g. max 3):

Use a Formula Tool:

AmountPrice1 = IF [RowIndex] = 1 THEN [SR_Billed_Price] ELSE NULL()
AmountPrice2 = IF [RowIndex] = 2 THEN [SR_Billed_Price] ELSE NULL()
YearStartDate1 = IF [RowIndex] = 1 THEN [Contract_Start_Date] ELSE NULL()

Then use a Summarise Tool (Max/First) to get one row per contract.

Option 2: Dynamic (for variable rows)

1. Add RowIndex per contract using Multi-Row Formula.

2. Transpose the fields (AmountPrice, YearStartDate, etc.).

3. Use Formula Tool: [Name] + ToString([RowIndex])
   → becomes AmountPrice1, YearEndDate2, etc.

4. CrossTab to pivot back to wide format.

Please try the above steps.