topic Re: Grouping and sorting in QlikView

Grouping and sorting

Anonymous — Wed, 12 Oct 2016 08:58:54 GMT

Hi All,

I have a scenario where I have Country,Product, Subproduct. My date, Order and Description are at subproduct level and I need to get the Description at product level based on foll logic:

First check subproduct date. choose the one with highest date. If two dates have same value, choose the one with lowest order. Take the description of that subproduct and assign it to Product.

Kindly advice how to do it.

PFA sample data.

Regards,

Ruchi

Re: Grouping and sorting

sunny_talwar — Wed, 12 Oct 2016 09:06:04 GMT

Try this:

Table:

LOAD Market,

Product,

SubProduct,

Product_Order,

[Pdt Valid From],

Pdt_Description

FROM

SampleData.xls

(biff, embedded labels, table is Sheet1$);

Left Join (Table)

LOAD Market,

Product,

FirstSortedValue(Pdt_Description, -([Pdt Valid From]*1000000 - Product_Order)) as New_Pdt_Description

Resident Table

Group By Market, Product;

Re: Grouping and sorting

Anonymous — Wed, 12 Oct 2016 09:48:14 GMT

Wow!!! That Works!! Thanks a lot!!

Re: Grouping and sorting

Anonymous — Wed, 12 Oct 2016 09:55:48 GMT

Thanks Sunny!!

Just to add, in the real model, the scenario extends further where date is also same and product order is also same, in such a situation New Pdt description shows blanks, whereas it is required to pick the first occurrence. Can you please advice. Thanks a lot!

Re: Grouping and sorting

sunny_talwar — Wed, 12 Oct 2016 10:01:30 GMT

May be something like this:

Table:

LOAD RowNo() as Key,

Market,

Product,

SubProduct,

Product_Order,

[Pdt Valid From],

Pdt_Description

FROM

SampleData.xls

(biff, embedded labels, table is Sheet1$);

Left Join (Table)

LOAD Market,

Product,

FirstSortedValue(Pdt_Description, -([Pdt Valid From] - Product_Order/1000000 - Key/1000000000)) as New_Pdt_Description

Resident Table

Group By Market, Product;

Re: Grouping and sorting

Anonymous — Wed, 12 Oct 2016 10:32:37 GMT

Works just fine!! Thanks a lot!