How to Unpivot a Table by using CLoverDx

CloverDX Platform
1

Hello Experts,

I just stuck here , I have huge list of Excel files.
Which I load into SQL server but before loading I am doing Unpivot Excel sheets manually as per SQL Tables structure.

I am looking some automatic feature in CLoverDx / ETL to transform and Unpivot my excel data.

I have this in excel -
Column1 COL1 COL2
AB CD
A 11 43
B 22 45

and I need this from CloverDx-

Column1 Attribute Value
A AB 11
A CD 43
B AB 22
B CD 45

Any idea which CLoverDx element support this feature …

2

Hello Nitish,
there are at least 2 options how you can approach this challenge both of which I have demonstrated in the attached graph:
1. Using the MetaPivot component

  • This component is quite elegant and easy to use because it does not require any configuration. It automatically converts every incoming record into several output records, each one representing a single field from the input.

  • Worth noting is that this component has to use its fixed format of the output metadata so you need to process the “unpivoted” data further into the desired format.

  • For that purpose, I have used a simple CTL transformation in Reformat.

2. Using the Normalizer component

  • The advantage of this component is that you can configure the entire transformation using CTL inside it.

  • In the count() function, you need to define the number of records that will be output from a single input record.

  • The transform(integer idx) function is called count() times for each input record. The idx argument specifies which output record is being created.

  • Worth noting is the usage of the getFieldName() and getStringValue() functions which can access record fields dynamically.

Regards,