topic Re: Help required in the aggregation. in QlikView

Help required in the aggregation.

qlikviewforum — Wed, 24 Jun 2015 12:56:31 GMT

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?

Re: Help required in the aggregation.

sunny_talwar — Wed, 24 Jun 2015 13:02:24 GMT

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

Re: Help required in the aggregation.

qlikviewforum — Wed, 24 Jun 2015 13:18:45 GMT

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.

Re: Help required in the aggregation.

sunny_talwar — Wed, 24 Jun 2015 13:32:41 GMT

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;

Re: Help required in the aggregation.

qlikviewforum — Wed, 24 Jun 2015 13:41:19 GMT

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;

Re: Help required in the aggregation.

sunny_talwar — Wed, 24 Jun 2015 13:44:56 GMT

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;

Re: Help required in the aggregation.

qlikviewforum — Wed, 24 Jun 2015 14:32:31 GMT

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.

Re: Help required in the aggregation.

sunny_talwar — Wed, 24 Jun 2015 14:33:58 GMT

Sure thing