Help required in the aggregation.

[qlikviewforum]

Due to come difficulties in the we have below piece of code (to do the aggregation) which connects to the FACT and we are using SALES_AMT in the expression. Since it is creating addition table in the data model we would like to remove this changes in the DM and we would like to this aggregation in the expression itself(So that we can use 'SALES' in the expression with required aggregation).

Load  PERIOD&'-'&CUS_ID&'-'&LEVEL as KEY_CUS_LINK,

  sum(SALES) as SALES_AMT

Resident FACT

group by PERIOD&'-'&CUS_ID&'-'&LEVEL;

Not really sure how to achieve this. Can someone help on this?

[sunny_talwar]

Firstly you don't really have to leave this as a separate table and can join it back to your FACT Table because doing it in script would be much more efficient then using a Aggr function on the front end. Having Said that you can use the following formula in the front end I guess:

Sum/Max/Min/Count(Aggr(Sum(Sales). PERIOD, CUS_ID, LEVEL))

HTH

Best,

Sunny

[qlikviewforum]

Ok I have already tried that but it didn't work. Not sure what is going wrong!!!

Buy the way what join should I be using so that existing granularity of the data doesn't change. Please help with sample load script.

[sunny_talwar]

Actually to make sure you don't multiply the number of rows, you can probably use Mapping rather than using a left join.

FACT:

LOAD PERIOD&'-'&CUS_ID&'-'&LEVEL as KEY_CUS_LINK,

          *,

FROM somewhere;

MappingTable:

Load  KEY_CUS_LINK,

         sum(SALES) as SALES_AMT

Resident FACT

group by KEY_CUS_LINK;

FINALFACT:

LOAD *,

          ApplyMap('MappingTable', KEY_CUS_LINK) as SALES_AMT

Resident FACT;

DROP Table FACT;

[qlikviewforum]

Ok thanks I will try that and update.

By the way if I had to left join you want me to do something like below right? Correct me if I am wrong.

FACT: (Orginal FACT table)

Load * From anyhere;

left join

Load  PERIOD&'-'&CUS_ID&'-'&LEVEL as KEY_CUS_LINK,

  sum(SALES) as SALES_AMT

Resident FACT

group by PERIOD&'-'&CUS_ID&'-'&LEVEL;

[sunny_talwar]

No, use this:

FACT:

Load * From anyhere;

left join

Load  PERIOD,

         CUS_ID,

         LEVEL ,

         sum(SALES) as SALES_AMT

Resident FACT

group by PERIOD, CUS_ID, LEVEL;

[qlikviewforum]

Ok thanks it would have been great if we can achieve this in the UI since we are already doing in the back end. Anyway will try the above and will let you know.

[sunny_talwar]

Sure thing