DataMigration: KingswaySoft using Pivot

Today, we will learn how to transform data into row from columns pattern.

For retrieving data using FetchXML, we may get data in vertical format like we may get multiple row fields. Depending on the entity relationship and data categorization.

Whenever we face such kind of scenario we need to opt for pivot component.

LRRequestIDLR_Split_AMTLR_name
Blog\0011\201986805.56ATM
Blog\0011\20199333.33DA
Blog\0012\201979320ES
Blog\0013\201930288.46IMS
Blog\0013\2019165270ATM
Blog\0013\201950000DA
Figure 1: Data Format when retrieved from FetchXML

I would say that Pivot component basically transform the data to view from different perspective.

Figure 2: Pivot Component
Figure 3: Pivot Editor

For Pivot we will be using same 3 attribute displayed in Figure 1.

LR_name will be our Pivot Key, LR_Split_AMT will be our Pivot Value and LRRequestID will be our Set Key.
Using this LR_name as a pivot key would split its value into different columns in the destination db and LRRequestID unique for each row.

LRRequestIDATMDAESIMS
Blog\0011\201986805.569333.33  
Blog\0012\2019  79320 
Blog\0013\201916527050000 30288.46
Figure 4: Output result

To achieve the result as shown in figure 4, we also need to perform some additional step.
Right click in the Pivot component and click on Show Advanced Editor option.
Inside the Input and Output Properties we will see the three columns(Pivot Key, Pivot Value and Set Key) attributes in the Input Columns tab.
Now, as per the desired output (Figure 4), we need the LR_Split_AMT to be captured in a single row inside the destination db against LRRequestID. Therefore we will need to take the LineageID of the LR_Split_AMT attribute as show in the below image under blue border.

Figure 5: Pivot Advance Editor

Post that we need to click on Output columns and hit Add Column button. Where we need to create 4 new columns against each LR_name.

  • **Points to Remember for all four new collumns:
  • Name the column as you desire
  • Under Custom Properties
    PivotKeyValue:
    Name the value in the exactly same manner how its appears when fetched from CRM.
    SourceColumn: Provide the LineageID of LR_Split_AMT as explained above.
Figure 6: Pivot Advance Editor

That’s all about Pivot component.

Lastly, we will need to map the newly created output columns to the destination db columns in their data flow for proper data presentation.

Thank You.. Hope this post helps anyone.

Create your website with WordPress.com
Get started
%d bloggers like this: