Equivalent for AGGR in script

[liviumac]

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

[rubenmarin · Accepted solution]

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]

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?

[rubenmarin]

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]

excellent, much appreciated