Skip to main content
Announcements
Global Transformation Awards! Applications are now open. Submit Entry
cancel
Showing results for 
Search instead 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?


Thank you in advance!!!

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

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

omarbensalemksomosera10

View solution in original post

11 Replies
OmarBenSalem

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.


OmarBenSalem

U can try to refer to this:

Rank Function load script

ksomosera10
Creator II
Creator II

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!

Anonymous
Not applicable
Author

Thank you for your info!

Anonymous
Not applicable
Author

Hi Kevin,

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

Qlik community 7.PNG

Thank you in advance!

ksomosera10
Creator II
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!

OmarBenSalem

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

Anonymous
Not applicable
Author

Thank you for your explanation!

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

But you help me to clarify.

Thank You Again!

ksomosera10
Creator II
Creator II

Good to hear!

Just ask if you need help^^