cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## Aggr and Rank in Load Script

Hello everyone,

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?

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

Based on information provided by Omar and Kevin,

I came out with a solution

newtable:

[Factory],

if([Total Complaint Received] = 0, 1,

Resident maintable,

Thank everyone!!!

11 Replies
MVP

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.

MVP

U can try to refer to this:

Creator II

Good day.

You can still use the functionality of AGGR on the Load Script.

For example:

* ,

If([Total Complaint Received]) = 0, 1) as [NewField]

FROM (File)

GROUP BY [Factory];

Thanks!

Anonymous
Not applicable
Author

Anonymous
Not applicable
Author

Hi Kevin,

I used your codes but then it shows this error. How to fix this??

Creator II

Hi,

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.

Thanks!

MVP

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.

Example:

u have this table:

IDAGESALES
12010
22010
22010
22010
32510
43010
43010
43010

If u want just a new table as follow:

aggr(Sum(Sales),ID)

U should do this:

Sum(Sales) as SalesByID

Resident urTable

group by ID;

Result:

IDSalesByID
110
230
310
430

if u want aggr(sum(Sales),Age)

U should do this:

Sum(Sales) as SalesByAge

Resident urTable

group by Age;

Result:

AgeSalesByAge
2040
2510
3030

Now if u want sales by Age then ID

aggr(Sum(Sales),Age, ID)

u should do :

Age,

Sum(Sales) as SalesByAgethenID

resident urTable

Group By Age,ID;

Result

AgeIDSalesByAgethenID
20110
20230
25310
30430

=> 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

Anonymous
Not applicable
Author