topic Re: Aggregation in QlikView

Aggregation

Thu, 27 Aug 2015 11:25:34 GMT

Hi All,

can any one help me on this

I have a fact table with 26 column , in that I want the Amount data group by "Device", "Hour", "Date" how can I make an aggregated qvd with that table.

I know we can use like the below

Load

Device,

Date,

Hour,

Sum(Amount),

xxxx,

yyyy,

from xxxx.qvd

[xxxxxx] group by

Device,

Date,

Hour

xxxxxxx

xxxxx,

yyyy,

yyyy;

But I want the data aggregated by Device,Date,Hour.

Regards

John

Re: Aggregation

sunny_talwar — Thu, 27 Aug 2015 11:29:16 GMT

May be do a left join into the fact table:

Table:

Load

Device,

Date,

Hour,

Amount,

xxxx,

yyyy,

from xxxx.qvd;

Left Join (Table)

LOAD Device,

Date,

Hour,

Sum(Amount) as TotalAmount

Resident Table

Group By Device, Date, Hour;

Re: Aggregation

Thu, 27 Aug 2015 11:40:12 GMT

Thanks Sunny I will give a try,

I have fact table size as Approx. 3GB [35 Million Records ] , if we load the table two times will it not impact on Loading time?

Re: Aggregation

sunny_talwar — Thu, 27 Aug 2015 11:43:10 GMT

I am sure it will, but left join or Mapping Load are the only two ways I can think of doing what you are looking to do and both ways will require you to load the table twice.

Re: Aggregation

Thu, 27 Aug 2015 11:46:27 GMT

Yes sunny, I am trying to optimize my application so I can't do that. Please let me know if any use full methods or links are there.

Re: Aggregation

sunny_talwar — Thu, 27 Aug 2015 11:50:58 GMT

Like I said, I don't happen to know another way to do this, so can't really share much, but for join and mapping load you can check this out:Don't join - use Applymap instead

Re: Aggregation

Thu, 27 Aug 2015 11:53:24 GMT

Thanks Sunny

Re: Aggregation

sunny_talwar — Thu, 27 Aug 2015 11:55:21 GMT

Your very welcome, I hope somebody comes up with a better solution

Sunny

Re: Aggregation

jonathandienst — Thu, 27 Aug 2015 12:03:43 GMT

Rather than joining the two, keep them as separate tables and let QV handle the association. If you don't want a synthetic key, you can create your own composite key like this:

Table:

Load Device,

Date,

Hour,

Autonumber(Device & '-' & Date & '-' Hour, 'aggkey') As Key,

Amount,

xxxx,

yyyy,

from xxxx.qvd;

Aggregated:

LOAD Autonumber(Device & '-' & Date & '-' Hour, 'aggkey') As Key,

Sum(Amount) as TotalAmount

Resident Table

Group By Device, Date, Hour;