topic Re: tnormalize mutli columns in Talend Studio

tnormalize mutli columns

sunteng — Fri, 22 Mar 2024 04:28:20 GMT

i have a txt file:

Tid|Product|Accounts|Transfers
1|[P01,P02]|[AC1,AC2]|[10.0,40.0]
2|[P01]||
3|[P01,P03]|[AC4,AC5]|[30.0,50.0]
4|[P01]||
5|[P01,P02,P03,P04,P05]|[AC1,AC2,AC3,AC4,AC5]|[10.0,40.0,20.0,30.0,50.0]

and my target

Tid ；product_id ；account_id；transfer

1 PO1 AC1 10

1 PO2 AC2 40

2 P01

3 P01 AC4 30

3 PO3 AC5 50

4 P01

5 PO1 AC1 10

5 PO2 AC2 40

5 P03 AC3 20

5 PO4 AC4 30

5 P05 AC5 50

how to write the code for that i try many times Thanks!

Re: tnormalize mutli columns

Ikram99 — Fri, 22 Mar 2024 12:10:29 GMT

I hope this load script can assist you.

[f_qli]:

LOAD

Tid,

Replace(Replace(Product, '[', ''), ']', '') as Product,

Replace(Replace(Accounts, '[', ''), ']', '') as Accounts,

Replace(Replace(Transfers, '[', ''), ']', '') as Transfers

FROM [lib://Documents/f_qli.txt]

(txt, codepage is 28592, embedded labels, delimiter is '|', msq);

NoConcatenate

FinalData:

LOAD

Tid,

Trim(SubField(Product, ',', IterNo())) AS Product,

Trim(SubField(Accounts, ',', IterNo())) AS Account,

Trim(SubField(Transfers, ',', IterNo())) AS Transfers

RESIDENT f_qli

WHILE IterNo() <= SubStringCount(Product, ',')+1;

Re: tnormalize mutli columns

sunteng — Sat, 23 Mar 2024 05:36:35 GMT

if i want to use tmap or other like tjavarow how to solve it thanks!

Re: tnormalize mutli columns

Shicong_Hong — Sun, 24 Mar 2024 14:07:11 GMT

Hello

You need to split the input data into three output table, process output separately and join them back again to merge all the columns.

For more details, please see the demo job. Please try it and let me know if you have any questions/issues.

Regards

Shicong