QlikView App Dev

[Anonymous]

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

[sunny_talwar · Accepted solution]

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]

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

[Anonymous]

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]

Works just fine!! Thanks a lot!