Announcements
cancel
Showing results for
Did you mean:
Contributor III

## Group by working

Hi Team,

tab:
player,score
p1,30
p2,40
p1,50
p3,50
p4,20
p2,100
p5,90
p3,100
];
tab2:
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

Labels (1)
• ### General Question

2 Replies

try below script

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

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
Contributor III
Author

that's not my query

thanks