Announcements
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

Im still green on this, never understand aggr function clearly.

But you help me to clarify.

Thank You Again!

Creator II

Good to hear!

Just ask if you need help^^

Tags
Community Browser