topic Re: Split comma separated values in column into rows and How to Rank Each customer Record Separately in Load Script in QlikView

Split comma separated values in column into rows and How to Rank Each customer Record Separately in Load Script

basav — Sat, 16 Nov 2024 17:43:30 GMT

Hi All,

I want to Split comma separated values in a column into rows, and then rank each Items row of Individual customer separately, please find my test script below:

Source_Data:

Load * Inline [

Group_Id,Items

Customer1,"Bread,Biscuit,Chocolate,Milk,Toothbrush,Flour,Soap"

Customer2,"Jean,Shirt,Flour, Shoes,Soap"

Cutomer3,"Biscuit, Bread"

];

NoConcatenate

main:
LOAD
Group_Id,
SubField(Items,',') as Items,
RecNo() as Rank
Resident Source_Data;

Drop Table Source_Data;

However, the above is script is giving results like this:

I know I can use Rank function in the front-end to get what I want, however the data that I have is about 10M rows, Hence I would Like to rank in the load script. Please help.

Below is the output I am looking for:

Re: Split comma separated values in column into rows and How to Rank Each customer Record Separately in Load Script

chrismarlow — Wed, 09 Dec 2020 09:13:31 GMT

Hi,

Try changing the second part to;

main: Load If(RecNo()=1,1, If(Peek(Group_Id)=Group_Id, Peek(Rank)+1,1)) AS Rank, *; LOAD Group_Id, SubField(Items,',') as Items Resident Source_Data;

Cheers,

Chris.

Re: Split comma separated values in column into rows and How to Rank Each customer Record Separately in Load Script

Taoufiq_Zarra — Wed, 09 Dec 2020 09:14:40 GMT

@basav Maye be like this :

Source_Data: Load * Inline [ Group_Id,Items Customer1,"Bread,Biscuit,Chocolate,Milk,Toothbrush,Flour,Soap" Customer2,"Jean,Shirt,Flour, Shoes,Soap" Cutomer3,"Biscuit, Bread" ]; NoConcatenate main: load *,if(peek(Group_Id)=Group_Id,peek(rank)+1,1) as rank; LOAD Group_Id, SubField(Items,',') as Items Resident Source_Data; Drop Table Source_Data;

output:

Re: Split comma separated values in column into rows and How to Rank Each customer Record Separately in Load Script

basav — Wed, 09 Dec 2020 09:46:53 GMT

@Taoufiq_Zarra @chrismarlow Both of your solution works thank you.