Trasformation from cols to rows

CloverDX Platform
1

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.

2

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.

3

Hi Jan,

thank you for this very helpful post, all is clear now :slight_smile:

many thanks.

Carlo.