# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
New Contributor III

## 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?

Thank you in advance!!!

1 Solution

Accepted Solutions
New Contributor III

## Re: Aggr and Rank in Load Script

Based on information provided by Omar and Kevin,

I came out with a solution

newtable:

Load

[Factory],

[Total Complaint Received],

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

if([Total Complaint Received] = peek([Total Complaint Received]), peek(Rank), recno()-1)) as Rank

Resident maintable,

Order by [Total Complaint Received]

Thank everyone!!!

11 Replies
Partner

## Re: Aggr and Rank in Load Script

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.

Partner

## Re: Aggr and Rank in Load Script

U can try to refer to this:

Rank Function load script

Contributor II

## Re: Aggr and Rank in Load Script

Hi Muhammad Hazim,

Good day.

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

Just use GROUP BY instead.

For example:

LOAD

* ,

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

FROM (File)

GROUP BY [Factory];

Thanks!

New Contributor III

## Re: Aggr and Rank in Load Script

Thank you for your info!

New Contributor III

## Re: Aggr and Rank in Load Script

Hi Kevin,

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

Thank you in advance!

Contributor II

## Re: Aggr and Rank in Load Script

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!

Partner

## Re: Aggr and Rank in Load Script

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:

load ID,

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:

load Age,

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 :

load ID,

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

Highlighted
New Contributor III

## Re: Aggr and Rank in Load Script

Thank you for your explanation!

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

But you help me to clarify.

Thank You Again!

Contributor II

## Re: Aggr and Rank in Load Script

Good to hear!

Just ask if you need help^^