topic Cleave values in one row on two rows in QlikView

Cleave values in one row on two rows

Mon, 24 Nov 2014 00:13:06 GMT

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

Re: Cleave values in one row on two rows

nagaiank — Mon, 24 Nov 2014 01:33:56 GMT

You may use the script given below:

load * Inline [

Account Name, Account Responsible, BI Tools

Customer1, AR1, "BO,BW"

Customer2, AR2, "Qlikview,BO"

Customer3, AR3, Microsoft BI

Customer4, AR4, Tableau

];

NoConcatenate

LOAD [Account Name]

,[Account Responsible]

,SubField([BI Tools],',') as Tool

Resident A;

Drop Table A;

Re: Cleave values in one row on two rows

SergeyMak — Mon, 24 Nov 2014 01:39:10 GMT

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

Re: Cleave values in one row on two rows

jagan — Mon, 24 Nov 2014 01:50:02 GMT

Hi,

Try like this

TableName:

LOAD

[Account Name],

[Account Responsible],

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

FROM DataSource;

Hope this helps you.

Regards,

Jagan.