topic Loop on columns in Talend Studio

Loop on columns

Anonymous — Thu, 11 Feb 2016 18:37:21 GMT

I have an input excel file with a Matrix like the one below.
I need to read this file and if there is an "X" in the space, write out the concatenated Folder+Form in an output variable.
At first I tried "X".equals(input.Folder1)?input.Folder1+input.Form:"X".equals(input.Folder2)?input.Folder2+input.Form:"X".equals(input.Folder3)?input.Folder3+input.Form:null
This gave me the first X in each row. I need to be able to write multiple lines if there are more than one X in a row. I have to loop it somehow, any ideas how to approach?
Form Folder1 Folder2 Folder3
Form1 X
Form2 X X X
Form3 X X

Re: Loop on columns

Anonymous — Fri, 12 Feb 2016 10:03:26 GMT

OK, there are two stages to this.
1) Use a tMap to to test each Folder column for an X. If it contains an X, concatenate the Form value with the Folder value. If there is more than 1 X in the row, then concatenate the concatenated values using a separator (I will use a pipe "|"). Like below....

From....

Form Folder1 Folder2 Folder3
Form1 X
Form2 X X X
Form3 X X

To.......

Form1Folder1
Form2Folder1|Form2Folder2|Form2Folder3
Form2Folder2|Form2Folder3

2) Next you need to create multiple rows out of the concatenated values. Use the tNormalize component for this. This is explained here ( https://help.talend.com/pages/viewpage.action?pageId=8123245). You will need to use pipe "|" as the separator in this example.

This will return....

Form1Folder1
Form2Folder1
Form2Folder2
Form2Folder3
Form2Folder2
Form2Folder3

Re: Loop on columns

Anonymous — Fri, 12 Feb 2016 13:51:32 GMT

How can I use the tMap to test each folder? In my post I was using "X".equals(object)?true:false statement strung together to check each column. But this only gives me the first instance X in each row, not all the X's in each row. How can I check for X in the column, for every column?

Re: Loop on columns

Anonymous — Fri, 12 Feb 2016 15:00:59 GMT

As an example, lets assume that you have columns "One", "Two", "Three" and "Four". You want to know if these columns hold any data. If they do, then use them in a String concatenation, if they don't then ignore them. You could achieve this using a tMap variable (the box inbetween the input and output). The code you would use would be something like this (assuming the input row is "row1" and a pipe "|" will be used as a separator)....

(row1.One!=null ? row1.One +"|" : "") + (row1.Two!=null ? row1.Two +"|" : "") + (row1.Three!=null ? row1.Three +"|" : "") + (row1.Four!=null ? row1.Four +"|" : "")

Then the variable you create is used to pass the resulting value to the output column.

The above example will do this to the following data....

One Two Three Four
a c
b d
a b c d

Result

a|c|
b|d|
a|b|c|d|

(Obviously some code would be need to remove the trailing pipe in this example)