Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Accepted Solutions
Hi,
I see 2 options :
1) use a tJavaFlex. Assuming you are confident that you'll never have more that 3 (for example) address, you can use a tJavaFlex followed by a tFilterRow to produce the desired result:
<data_source> --(main)--> tJavaFlex --(main)--> tFilterRow
The idea is to create 3 output rows for each input rows and in each output row, put the nth item of the input row if it exists. The tFilterRow will simply discard rows with no data at all (or no address or whatever).
The tJavaFlex code will contain something like this :
BEGIN (replace 3 with the maximum number of data you can have) :
for (int i=0; i<3; i++) {
MAIN (row1 is the input row, row2 the output):
String[] add;
String[] email;
String[] phone;
row2.id = row1.id;
add=row1.add.split("~", -1);
if (add != null && add.length > i)
row2.add=add[i];
else
row2.add="";
email=row1.email.split("~", -1);
if (email != null && email.length > i)
row2.email=email[i];
else
row2.email="";
phone=row1.phone.split("~", -1);
if (phone != null && phone.length > i)
row2.phone=phone[i];
else
row2.phone="";
Please note that it's certainly possible to optimize this code! Take this as a guideline
The idea is to split each input data and take the nth item if it exists.
2) Another option would be to first split your data into 3 table (with a tMap for example) :
id,address / id,email / id,phone
You then normlize each table separatly and add a sequence for each "row" of the same id:
id,row,address / id,row,email / id,row,phone
with row being something such as Numeric.sequence("address_"+id,1,1)
And at the end, you join all 3 datasets back.
I think it should also work assuming the order is preserved will going through the tNormalize.
In doubt, I would probably prefer the first option: it has more java code but it's probably safer and more efficient.
Regards.
Hi,
I see 2 options :
1) use a tJavaFlex. Assuming you are confident that you'll never have more that 3 (for example) address, you can use a tJavaFlex followed by a tFilterRow to produce the desired result:
<data_source> --(main)--> tJavaFlex --(main)--> tFilterRow
The idea is to create 3 output rows for each input rows and in each output row, put the nth item of the input row if it exists. The tFilterRow will simply discard rows with no data at all (or no address or whatever).
The tJavaFlex code will contain something like this :
BEGIN (replace 3 with the maximum number of data you can have) :
for (int i=0; i<3; i++) {
MAIN (row1 is the input row, row2 the output):
String[] add;
String[] email;
String[] phone;
row2.id = row1.id;
add=row1.add.split("~", -1);
if (add != null && add.length > i)
row2.add=add[i];
else
row2.add="";
email=row1.email.split("~", -1);
if (email != null && email.length > i)
row2.email=email[i];
else
row2.email="";
phone=row1.phone.split("~", -1);
if (phone != null && phone.length > i)
row2.phone=phone[i];
else
row2.phone="";
Please note that it's certainly possible to optimize this code! Take this as a guideline
The idea is to split each input data and take the nth item if it exists.
2) Another option would be to first split your data into 3 table (with a tMap for example) :
id,address / id,email / id,phone
You then normlize each table separatly and add a sequence for each "row" of the same id:
id,row,address / id,row,email / id,row,phone
with row being something such as Numeric.sequence("address_"+id,1,1)
And at the end, you join all 3 datasets back.
I think it should also work assuming the order is preserved will going through the tNormalize.
In doubt, I would probably prefer the first option: it has more java code but it's probably safer and more efficient.
Regards.
