Cleave values in one row on two rows

[#1]

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

[nagaiank]

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;

[SergeyMak]

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
];

[jagan]

Hi,

Try like this

TableName:

LOAD

[Account Name],

[Account Responsible],

SubField([BI Tools], ',') AS [BI Tools]

FROM DataSource;

Hope this helps you.

Regards,

Jagan.