Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everybody,
I've an Excel file which datas contains severals values in one row separated with a coma, like that:
Account Name Account Responsible BI Tools
Customer1 AR1 BO,BW
Customer2 AR2 QlikView,BO
Customer3 AR3 Microsoft BI
Customer4 AR4 Tableau
I want to write a script to get a table like the sample below:
Account Name Account Responsible BI Tools
Customer1 AR1 BO
Customer1 AR1 BW
Customer2 AR2 QlikView
Customer2 AR2 BO
Customer3 AR3 Microsoft BI
Customer4 AR4 Tableau
Could someone help me please?
Thank you
You may use the script given below:
A:
load * Inline [
Account Name, Account Responsible, BI Tools
Customer1, AR1, "BO,BW"
Customer2, AR2, "Qlikview,BO"
Customer3, AR3, Microsoft BI
Customer4, AR4, Tableau
];
B:
NoConcatenate
LOAD [Account Name]
,[Account Responsible]
,SubField([BI Tools],',') as Tool
Resident A;
Drop Table A;
Hi,
It would be faster and easier if you just use preceding load
Data:
LOAD
[Account Name],
[Account Responsible],
Subfield([BI Tools], ',') AS [BI Tool]
Inline [
Account Name, Account Responsible, BI Tools
Customer1, AR1, "BO,BW"
Customer2, AR2, "Qlikview,BO"
Customer3, AR3, Microsoft BI
Customer4, AR4, Tableau
];
Hi,
Try like this
TableName:
LOAD
[Account Name],
[Account Responsible],
SubField([BI Tools], ',') AS [BI Tools]
FROM DataSource;
Hope this helps you.
Regards,
Jagan.