topic Re: Aggregate values in a cross table in QlikView

Aggregate values in a cross table

Mon, 20 Oct 2014 12:13:36 GMT

Hello everyone,

I need your help about an aggregate in a cross table.

You an see in that example that I have several invoices numbers for only one order.

Therefore, all my lines are duplicated with the same content.

Do you know how can I aggregate the invoice number, to have in this example a result like that :

Thanks lot for your help

Re: Aggregate values in a cross table

Anonymous — Mon, 20 Oct 2014 12:24:05 GMT

HI!

You can try using this on your expression:

=Concat(Distinct [Invoice Number],',')

There's attached an example using this.

Regards,

Gabriel

Re: Aggregate values in a cross table

JonnyPoole — Mon, 20 Oct 2014 12:25:02 GMT

You can delimit the invoice numbers with the concat() function. Probably something like this

=concat( Distinct total <[Order Number]> [Invoice Number] , ', ' ) in an expression

This can be done in a chart expression, or you can build the data model this way in the load script.

Doing the latter will eliminate any duplicate records from the data model and you won't have to repeat any chart conditions when dealing with the duplicates elsewhere in the dashboard.

Otherwise you can do a NULL filter in the expression to get rid of the duplicates. Post the spreadsheet data and i'll post formula.

Re: Aggregate values in a cross table

Mon, 20 Oct 2014 12:34:11 GMT

Hello,

Thanks to both of you.

Your methods works fine with the concat as expression.

But it is possible to do this for a dimension ?

Thank you again !

Re: Aggregate values in a cross table

JonnyPoole — Mon, 20 Oct 2014 12:37:24 GMT

It is. Use aggr() in lieu of total <[Order Number]> as follows in the formula of a calculated dimension

=aggr( concat( Distinct [Invoice Number] , ', ' ), [Order Number])

Re: Aggregate values in a cross table

Mon, 20 Oct 2014 12:42:45 GMT

It works very fine.

Thanks lot !

Re: Aggregate values in a cross table

Mon, 20 Oct 2014 12:49:20 GMT

Thanks this document is useful !

Re: Aggregate values in a cross table

Anonymous — Mon, 20 Oct 2014 12:57:06 GMT

Hi,

You can also do aggregation at the script level.

Example:

Load Section,Order_Number,Order_Designation,Order_Supplier,Order_Date,Concat(Invoice_Number,',')

resident [table_name]

group by Section,Order_Number,Order_Designation,Order_Supplier,Order_Date;

Thanks ,

Geeta