Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

liviumac
New Contributor III

Equivalent for AGGR in script

Hello

I want to calculate in the script a dimension that I would do in a chart using the AGGR function and I cannot due that because AGGR does not work in the script

so here is what I want to do:

suppose I have the following data structure: Client, Region, Sales

I want to be able to identify all clients with sales above a certain value and I want to mark them directly in the table through a calculated dimension: if the sum of all sales per customer is larger than X, then flag the client

in the chart I would use if(aggr(sum(sales),client)>=1000,'Flag','Non-flag') but this does not work in the script

any help is kindly appreciated

I assume it can be done with resident tables but I do not know how to do it

LM

1 Solution

Accepted Solutions

Re: Equivalent for AGGR in script

Hi Liv, the Aggr creates a temporal table with the dimensions used as parameters, you can replicate this in script using group By:

LOAD client,

     If(Sum(sales)>=1000,'Flag','Non-Flag') as FlagField

Resident TableName

Group by client;

4 Replies

Re: Equivalent for AGGR in script

Hi Liv, the Aggr creates a temporal table with the dimensions used as parameters, you can replicate this in script using group By:

LOAD client,

     If(Sum(sales)>=1000,'Flag','Non-Flag') as FlagField

Resident TableName

Group by client;

liviumac
New Contributor III

Re: Equivalent for AGGR in script

hi, thank you for the reply

does this table need to be a separate table linked with the original one via Client ID or is there a way to bring this flag into the original table and drop this table?

Re: Equivalent for AGGR in script

Hi, you can left as a separate table but usually is better to set flags in the same table.

Also it's recomended to use '0' and '1' to flag, it's smaller.

You can use Join or Map:

Join version (links by client field)

Left Join (MainTableName)

LOAD client,

    If(Sum(sales)>=1000,1,0) as FlagField

Resident MainTableName

Group by client;

Map version (use mapping table with the client values and applymap to look for the value)

mapFlag:

Mapping LOAD client as ValueToSearch,

    If(Sum(sales)>=1000,1,0) as ValueToReturn

Resident MainTableName

Group by client;

RENAME MainTableName to MainTableNameAux:


MainTableName:

LOAD *,

    ApplyMap('mapFlag', client, Null()) as FlagField

resident MainTableNameAux;


DROP Table MainTableNameAux;

liviumac
New Contributor III

Re: Equivalent for AGGR in script

excellent, much appreciated

Community Browser