How to Unpivot a Table by using CLoverDx

Support/help with CloverETL (4.9) and CloverDX (5.0 or newer) implementation problems

nitishkatiyar
Posts: 1
Joined: Wed Aug 26, 2020 12:32 pm

How to Unpivot a Table by using CLoverDx

Postby nitishkatiyar » Wed Aug 26, 2020 12:56 pm

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

bartonv
Posts: 145
Joined: Wed May 03, 2017 12:10 pm

Re: How to Unpivot a Table by using CLoverDx

Postby bartonv » Thu Aug 27, 2020 9:37 am

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,
Attachments
unpivot.grf
(7.17 KiB) Downloaded 14 times
---
Vladimir Barton
CloverCARE Support
CloverDX

Visit us online at http://www.cloverdx.com


cron