Trasformation from cols to rows

carlo.balbo

• October 03, 2012 00:00

• Answered

Follow

Hi All,

thank you in advance for your support.

My scenario is:

File INPUT with the columns Field1,Field2,Field3,Field4,Field5,Field6 where col. Field3 is a "multi value" for example:

Field separator ;
Field string enclosed by "

Sheen;Charlie;"MEMBER_ALL#Member All Description,J2EE_ADMIN#J2EE Developer,DBA_ADMIN#DBA Administrator";987766;8237-01;SHEENC;

The desired file Output :

Filed6,Filed3[0]
Filed6,Filed3[1]
Filed6,Filed3[2]

SHEENC,"MEMBER_ALL#Member All Description"
SHEENC,"J2EE_ADMIN#J2EE Developer"
SHEENC,"DBA_ADMIN#DBA Administrator"

I want to ask you what is the best way to get this.

Thanks,
Carlo.

• 

  slechtaj

  • October 05, 2012 15:22

  0

  Comment actions Permalink

  Hi Carlo.balbo,
  
  The whole transformation may be handled by only one component. The component that could parse input data row into several rows is Normalizer. However, you need to set up this component properly. In the first phase you trim the quotation marks from Filed3. Then you can split this record and assign to a list (item[]). This is written as a part of count() function. In the second phase you only need to send the data to output ports. Function transform() is called for each record extracted from the Field3.
  
  Please see the following Normalizer CTL code.
  

  
  //#CTL2
  
  string[] item;
  string x;
  
  function integer count() {
  	x = replace($in.0.Field3,"\"","");
  	item = split(x,",");
  	return length(item);
  }
  
  function integer transform(integer idx) {
  	$out.0.Field6 = $in.0.Field6;
  	$out.0.Field3 = "\"" + item[idx] + "\"";
    return OK;
  }
   

  
  Please keep in mind that format of the output data depends on its metadata, or any writer component settings. In your case, if you want to write these data into a file, you only need to add UniversalDataWriter after your normalizer and set metadata (with columns Field6 and Field3 delimited by comma) on the edge between Normalizer and UniversalDataWriter.
  For more information about Normalizer, please refer to http://doc.cloveretl.com/documentation/UserGuide/index.jsp?topic=/com.cloveretl.gui.docs/docs/normalizer.html.
• 

  carlo.balbo

  • October 08, 2012 12:09

  0

  Comment actions Permalink

  Hi Jan,
  
  thank you for this very helpful post, all is clear now :-)
  
  many thanks.
  
  Carlo.

Please sign in to leave a comment.