Discussion board where members can get started with Qlik Sense.
I have a problem where i need to convert these code into a readable code in the load script.
If( [Total Complaint Received] = 0, 1, Aggr(Rank(-Sum([Total Complaint Received]), 1,1), Factory) - 1)
As we all know, both Aggr and Rank function cannot use in the load script. Does anyone know how?
Thank you in advance!!!
Based on information provided by Omar and Kevin,
I came out with a solution
[Total Complaint Received],
if([Total Complaint Received] = 0, 1,
if([Total Complaint Received] = peek([Total Complaint Received]), peek(Rank), recno()-1)) as Rank
Order by [Total Complaint Received]
for rank, u can use AutoNumber(VALUE) as Rank
and order your table the way u want to rank it (using Order By clause)
Then, for Aggr, u replace it by GROUP BY clause.
Hi Muhammad Hazim,
You can still use the functionality of AGGR on the Load Script.
Just use GROUP BY instead.
If([Total Complaint Received]) = 0, 1) as [NewField]
GROUP BY [Factory];
By using 'Group By' you should include other fields except the [Rank]
Because I have no idea about the fields that you have, I just used the one field.
I hope it makes sense.
If you still encounter an error just beep me up.
to use Group by in the script;
u should use an aggr function (sum / count, only etc) and group ur calculation by all the other dimensions.
u have this table:
If u want just a new table as follow:
U should do this:
Sum(Sales) as SalesByID
group by ID;
if u want aggr(sum(Sales),Age)
U should do this:
Sum(Sales) as SalesByAge
group by Age;
Now if u want sales by Age then ID
u should do :
Sum(Sales) as SalesByAgethenID
Group By Age,ID;
=> You should always group by all the other dimensions in ur table; and deopends on what u want, make sure to group in the right order.
The first dimension would be the first grouping and so on
Hope that was clear and helps