tnormalize mutli columns

[sunteng]

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!

[Ikram99]

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; 

[sunteng]

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

[Shicong_Hong]

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