How to iterate for each row and denormalize

[VishaalRamdowar]

Hi,

I am build a job where I need to move the rows of a file to another row in the output file. 

Below is a screenshot of what needs to be done.

For each row E (highlighted in yellow), the HT values for row R should b moved to the T.

[Anonymous]

Can you post the file format as text please? I'll happily knock up an example, but I don't really want to copy the format from a screenshot 🙂

[VishaalRamdowar]

here you rhall.

The header begins with E and ends with trailer T.

testHong_Inv_F_MALHERBE_310120.csv

[Anonymous]

Right, I have knocked up a quick example which I will briefly describe. The job looks like this....

The output from the file you sent me looks like this....

E;testHong_F1;F;Cli1;KARCHER SAS;
T;1000.00;20.00;1758.20;10549.20;|testHong_F1_R1|testHong_F1_R2|testHong_F1_R3
E;testHong_F2;A;Cli2;KARCHER SAS;
T;2000.00;20.00;1758.20;10549.20;|testHong_F2_R1|testHong_F2_R2

The tFileInputDelimited just reads in the file data. Nothing special is done there. 

In the first tMap I do a couple of things. It looks like this.....

I first split the data. The top output is filtered using this filter....

row7.S.equals("E") || row7.S.equals("T")

The bottom output is filtered like this....

row7.S.equals("R")

I have decided to leave out the other "S" values as you didn't specify them in the original question.

I also create 2 tMap variables. These are "HeaderKey" and "Data". The HeaderKey is to join the two outputs together again. I use this code to identify the "HeaderKey" value ....

row7.S.equals("E") ? row7.HT : Var.HeaderKey  

This essentially sets a new "HeaderKey" every time an "S" record of value "E" appears. 

The "Data" value is created using this code....

row7.S.equals("E") ? "" : row7.S.equals("R") ? Var.Data + "|" + row7.HT : Var.Data  

This is a little more complicated. It does three things. If the "S" column is "E", it resets the value to an empty String (""). If the "S" column is "R" it sets the value to itself + "|" + the value of "HT". If the "S" value is anything else, it just sets the value of "Data" to itself. This builds up a pipe separated String for each each "HeaderKey".

The top output gets sent to tHashOutput_1. This is our main data. The bottom output gets sent to a tAggregateRow component before being sent to the tHashOutput_2. This is our list data. The tAggregateRow is shown below....

This component groups by the HeaderKey column and returns the last row for the Data column for each group. The rows are sent to the tHashOutput_2.

The second subjob is where the data is joined up. Essentially the tMap is the important component here. Here is how it is configured...

You will see that I am using an Inner join on the HeaderKey to join the data. Other than that, I am simply coding it to only output the list data when the "S" column has a value of "T". The code for that is in the "List" column of the output.....

row8.S.equals("T") ? row9.Data  : "" 

I have kind of rushed through this a little, assuming you have enough knowledge to work out what you need to do from this. You will probably want to include your other data, but I wasn't sure how to do this, so left it out. You should be able to build this into this solution.