Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
hazimalif17
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
hazimalif17
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!!!

omarbensalemksomosera10

View solution in original post

11 Replies
Partner
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
Partner

Re: Aggr and Rank in Load Script

U can try to refer to this:

Rank Function load script

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

hazimalif17
New Contributor III

Re: Aggr and Rank in Load Script

Thank you for your info!

Highlighted
hazimalif17
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??

Qlik community 7.PNG

Thank you in advance!

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

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

ksomosera10
Contributor II

Re: Aggr and Rank in Load Script

Good to hear!

Just ask if you need help^^