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!!!
Solved! Go to Solution.
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