topic Re: Group by working in App Development

Group by working

vajid4525 — Tue, 23 Jul 2024 06:22:38 GMT

Hi Team,

tab:
load * inline [
player,score
p1,30
p2,40
p1,50
p3,50
p4,20
p2,100
p5,90
p3,100
];
tab2:
load player1 ,if(isNull(score1+previous(score1)),score1,score1+previous(score1)) as score1;
load player as player1,sum(score) as score1 Resident tab group by player;

if I try to write algorithm for above script whether it would be as below:

key_index={}

key_sum_arr=[]

key_previous_arr=[]

score_array=[]

final=[]

iterating through each rows and updating key_index and adding score to array based on index

key_index={p1:0,p2:1,p3:2,p4:3,p4:4}

score_array=[[30,50],[40,100],[50,100],[20],[90]]

iterating through key_index and taking sum of score_array based on key and index and the previous function will take the last appended element in an array.

key_sum_arr=[80,140,150,20,90]

key_previous_arr=[80,220,290,170,110]

iterate through key_index and update final array by adding element from key_sum_arr and key_previous_arr based on key_index value

final=[[p1,80,80],[p2,140,220],[p3,150,290],[p4,20,170],[p5,90,110]]

final is the required output.

Thank you

Re: Group by working

PrashantSangle — Tue, 23 Jul 2024 06:52:13 GMT

try below script

tab:
load * inline [
player,score
p1,30
p2,40
p1,50
p3,50
p4,20
p2,100
p5,90
p3,100
];

NoConcatenate
tab1:
Load * Resident tab order by player;

NoConcatenate
tab2:
Load player, sum(score) as Total_score Resident tab1 Group by player;

NoConcatenate
final_tab:
Load player, Total_score,Total_score+if(IsNull(Previous(Total_score)),0,Previous(Total_score)) as Previous_score Resident tab2;

Drop Table tab,tab1,tab2;

Regards,

Prashant Sangle

Re: Group by working

vajid4525 — Tue, 23 Jul 2024 07:10:01 GMT

that's not my query

thanks