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

[basav]

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:

[chrismarlow · Accepted solution]

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.

[Taoufiq_Zarra · Accepted solution]

@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:

[basav]

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