Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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

1 Solution

Accepted Solutions
sunny_talwar

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
sunny_talwar

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;

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

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!

sunny_talwar

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;

Anonymous
Not applicable
Author

Works just fine!! Thanks a lot!