Aggregation

[#1]

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,

xxxx,

yyyy,

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

[sunny_talwar]

May be do a left join into the fact table:

Table:

Load

Device,

Date,

Hour,

Amount,

xxxx,

xxxx,

yyyy,

yyyy,

from xxxx.qvd;

Left Join (Table)

LOAD Device,

Date,

Hour,

Sum(Amount) as TotalAmount

Resident Table

Group By Device, Date, Hour;

[#3]

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?

[sunny_talwar]

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.

[#5]

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.

[sunny_talwar]

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

[#7]

Thanks Sunny

[sunny_talwar]

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

Sunny

[jonathandienst]

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,

  xxxx,

  yyyy,

  yyyy,

from xxxx.qvd;

Aggregated:

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

  Sum(Amount) as TotalAmount

Resident Table

Group By Device, Date, Hour;