topic Aggregate function at script level - using QVD in QlikView

Aggregate function at script level - using QVD

sureshbaabu — Mon, 21 Jan 2013 18:06:02 GMT

Hello,

I have a requirement to apply a SUM function on certain columns - group by at a different column.

So, is it possible to use a Aggregate function at script level while importing data from a QVD file. Please share your thoughts.

NOTE: I can't do the aggregation while creating the QVD file (connecting to Database) ;since the columns are coming from different tables

Eg:

Original Script: Load A,B,C,D from test.qvd

Required: Load A, Sum(B) as B1, Sum(C) as C1,D from test.qvd group by A;

Thanks,

Suresh

Re: Aggregate function at script level - using QVD

fernando_tonial — Mon, 21 Jan 2013 18:19:34 GMT

Hi Suresh,

yes it is possible, you should put the group by all fields that are no aggregate function.

Load A, Sum(B) as B1, Sum(C) as C1,D from test.qvd group by A,D;

Best Regards.

Tonial.

Re: Aggregate function at script level - using QVD

nagaiank — Mon, 21 Jan 2013 18:20:21 GMT

Yes, the following script will work.

Load A, Sum(B) as B1, Sum(C) as C1, Sum(D) as D1 from test.qvd group by A;

Re: Aggregate function at script level - using QVD

sureshbaabu — Tue, 22 Jan 2013 03:00:20 GMT

Hello Tonial,

Thanks for your response. I tested with a sample data and it works having all the un-aggregated columns in group by,

But In my real senario, I have close to 30 cloums that doest have any aggregate function. So, would you suggest using all 30 columns in the group by. Please share your thoughts.

Thank you!!!

Re: Aggregate function at script level - using QVD

sureshbaabu — Tue, 22 Jan 2013 03:09:33 GMT

Hello Krishnamoorthy,

Thanks for the Response. You Script is correct too, when I have my column D as numeric.

But, few of my columns are having characters, So, I believe having them on Group by will work.

Thanks

Re: Aggregate function at script level - using QVD

fernando_tonial — Tue, 22 Jan 2013 10:53:45 GMT

Hi Suresh,

Yes, it works the same as SQL.
You need to put all columns without aggregate function in group by.

Best Regards.

Tonial.

Re: Aggregate function at script level - using QVD

sureshbaabu — Tue, 22 Jan 2013 11:00:53 GMT

Thanks a lot for the clarification.

Re: Aggregate function at script level - using QVD

Wed, 22 Jul 2015 10:57:09 GMT

hi, Tonial

how to this qvd file in right