Transposing rows into columns with column headers & column values

CloverDxNewBie

• July 26, 2024 12:08

Follow

Dear Support,

We have a data as below:

and we wanted to see the output as below:

I have tried using MetaPivot, it did transpose the rows into columns but I am unable to group it by "Code", I wanted to group every single field with a unique identifier and in the above example it's "Code".

I hope i have explained it correctly.

Please if anyone can help, thank you very much in advance.

 

• 

  Ladislav Szabo

  • July 29, 2024 12:35

  • Official comment

  Comment actions Permalink

  Hello there,

   

  In this scenario, using a Normalizer component instead of the MetaPivot component simplifies the process, as it eliminates the need for extra components to transpose the data. The Normalizer component enables you to generate multiple records from a single record.

  Let’s assume you have prepared metadata on the input and output of the Normalizer component as follows:

  

  

  (I’ve set all the field types to string to keep the CTL example simple without any data type conversions)

   

  With the metadata set, we only need to configure the component’s Normalize property which consists of typing a few lines of CTL code.

  The first function count() is called for every input record and tells the component how many output records to create from the input record. Based on your example, I assume we always want to generate four output records for each input record. Therefore, we can hardcode the function to return number 4.

  // It parses each single input record and returns the number of records
  // that should be created from such input record.
  function integer count() {
  	return 4;
  }

   

  The following transform() function is called for every output record we create. Here, we can map the code of the input record and the corresponding field name and value using the record functions as shown below:

  // The idx argument specifies which output record is being created,
  // its values range is from 0 to count() - 1.
  function integer transform(integer idx) {
  	$out.0.Code = $in.0.Code;
  	$out.0.FieldName = getFieldName($in.0, idx + 1);
  	$out.0.FieldValue = getStringValue($in.0, idx + 1);
  
  	return OK;
  }

   

  I hope this helps!

  Kind regards.

   

Please sign in to leave a comment.