How to Unpivot a Table by using CLoverDx

nitishkatiyar

• August 26, 2020 00:00

• Answered

Follow

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 ..............

• 

  Vladimir Barton

  • August 27, 2020 08:37

  0

  Comment actions Permalink

  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,

  • unpivot.grf

Please sign in to leave a comment.