Regarding Consolidation Logic handling in Workflow

Question

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

Source:

Contract_NumberContract_Item_NumberProduct_CodeAnnualized_ValueContract_start_dateContract_end_date3051575850020000142735209.092026-06-082027-06-07

3051575874020000142734914.232025-06-082026-06-07

3051575850020000142734592.742024-06-08 2025-06-07

Target:

Contract_NumberContract_Item_NumberProductCode__cAmountPrice__cAmountPrice2__cAmount_Price_3__cAmount_Price_4__cAmount_Price_5__cAmount_Price_6__cAmount_Price_7__cEndDate__cAmountAllocatedFinal__cYear2AmountAllocatedFinal__cYear3AmountAllocatedFinal__cYear4AmountAllocatedFinal__cYear5AmountAllocatedFinal__cStartDate__cYear1Amount__cYear1EndDate__cYear1StartDate__cYear2Amount__cYear2EndDate__cYear2StartDate__cYear3Amount__cYear3EndDate__cYear3StartDate__c3051575850020000142734592.744914.235209.09    6/7/20274592.744914.235209.09  6/8/20244592.746/7/20256/8/20244914.236/7/20266/8/20255209.096/7/20276/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.

VinodKammula28 · Answer

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)

* Add RowIndex per contract using Multi-Row Formula.

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

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

* CrossTab to pivot back to wide format.

Please try the above steps.

VinodKammula28 · Answer

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.

VinodKammula28 · Answer

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:

plaintextIF [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:

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

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

target_dataset.csv

source_dataset.csv