topic Re: Aggregation in QlikView

Aggregation

Thu, 08 Oct 2015 11:17:33 GMT

Hi Qlikheads

can any one help me on the below scenario.

I have I table which is having 30 fields, in those 30 fields 7 are Amount measures these amounts I wanted to aggregated only on 5 fields, I don't want the remaining fields in group by clause, is it possible to do this in one table along with the remaining 18 fields. I don't want to split this table into two tables.

thanks in advance

John

Re: Aggregation

swuehl — Thu, 08 Oct 2015 11:22:14 GMT

I don't want the remaining fields in group by clause, is it possible to do this in one table along with the remaining 18 fields.

You don't want the 18 fields in the group by clause and you don't want to aggregate them.

I would say, it's not possible what you want, but I am not sure I know what you want. Can you elaborate with a simple sample?

Re: Aggregation

Thu, 08 Oct 2015 12:04:47 GMT

Thanks swuehl,

scenario:

let's say I have amount fields like below

Sales Amount

Pay Amount

Discount Amount

Credit Amount

Change Amount

Charge Amount

Actual Amount

fields need in aggregation

Date,

Name,

Date Time[mm/dd/yyyy hh]

Firm

Branch

remaining fields

xxxx

xxx

xxxx

total 250 Million record per year

for this data I would like to have the aggregated information in to one table. not in two different tables.

Please let me know if it is not clear.

John

Re: Aggregation

swuehl — Thu, 08 Oct 2015 12:17:35 GMT

So you want e.g. an aggregated Sales Amount per combination of Date, Name, Date Time, Firm and Branch ( I think you need only the lowest granularity to group by, maybe like Date Time, Name, Firm (Branch could be derived from Firm and Date from Date Time) ).

But it's still unclear how the remaining fields should be handled. Could you give an example with some sample lines of mock up data?

Re: Aggregation

avinashelite — Thu, 08 Oct 2015 12:19:44 GMT

as swuehl‌ mentioned please provide the sample app and data. It will be helpful for us to understand

Re: Aggregation

Thu, 08 Oct 2015 14:01:08 GMT

Thanks swuehl, I have attached the sample data ,

and

there I want

CashAmount
CashPaymentCount
ChangeAmount
SubTotalAmount
TaxAmount
ProspectAmount
TotalAmount

aggregated by
DateTime
ModeofSale
BranchName
ShiftNumber

and all other fields as is

I have more than 350 million records per a year. for this I want to built an aggregated qvd based on DateTime upto hour level