Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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.
@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:
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.
@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:
@Taoufiq_Zarra @chrismarlow Both of your solution works thank you.