Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Contributor

Grouping and sorting

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

Tags (1)
1 Solution

Accepted Solutions
Highlighted

Re: Grouping and sorting

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;

View solution in original post

5 Replies
Highlighted

Re: Grouping and sorting

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;

View solution in original post

Highlighted
Contributor

Re: Grouping and sorting

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

Highlighted
Contributor

Re: Grouping and sorting

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!

Highlighted

Re: Grouping and sorting

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;

Highlighted
Contributor

Re: Grouping and sorting

Works just fine!! Thanks a lot!