topic Advanced aggregation problem in QlikView

Advanced aggregation problem

Anonymous — Fri, 17 Feb 2012 09:56:34 GMT

Hi, If you check the attached file you will see a table with 375 records.I need to know how many of this are distinct based on the first field, but only the first 10 characters, and from those, just the ones that ShIppment SHipped field is different from 0.I've made several tests and this is the best I can come with :

aggr(distinct count(if(Shipment_Shipped=0,0,left(Shipment_Order_ShipId_Line,10))),Key_Date)

but this gives me again the 375 records.I'm not sure what to do now, Any ideas? thanks in advance to anyone who can help me...

Advanced aggregation problem

CELAMBARASAN — Fri, 17 Feb 2012 10:32:18 GMT

Instead of doing this in expresssion try to make a separate field

Load

Shipment_Order_ShipId_Line,

Left(Shipment_Order_ShipId_Line,10) as Shippment_Order,

Shipment_Shipped

From DataFile;

Then use expression as Count({<Shipment_Shipped={0}>} Distinct Shippment_Order)

It will give you the correct result

Celambarasan

Advanced aggregation problem

Fri, 17 Feb 2012 10:41:59 GMT

Hi,

I think Celambarasan's solution is a good one.

As you want result for ShipmentShipped different from 0, I believe the expression should be

Count({<Shipment_Shipped-={0}>} Distinct Shippment_Order)

kind regards