Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

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

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

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
Creator
Creator

Re: Grouping and sorting

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

Highlighted
Creator
Creator

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
Creator
Creator

Re: Grouping and sorting

Works just fine!! Thanks a lot!