Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Cleave values in one row on two rows

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

3 Replies
nagaiank
Valued Contributor III

Re: Cleave values in one row on two rows

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;

Capture1.PNG.png

sergey_maka8
Valued Contributor

Re: Cleave values in one row on two rows

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


MVP
MVP

Re: Cleave values in one row on two rows

Hi,

Try like this

TableName:

LOAD

[Account Name],

[Account Responsible],

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

FROM DataSource;


Hope this helps you.


Regards,

Jagan.

Community Browser