Value based on max date in script

[#1]

I have Sample Data like below. But in real there will be multiple products

Date	Product	Value
10 Jan 16	A	100
11 Jan 16	A	200
12 Jan 16	A	150
11 Jan 16	B	200
12 Jan 16	B	250
13 Jan 16	B	100

I need the Output like below

Date	Product	Value	Latest Product Value
10 Jan 16	A	100
11 Jan 16	A	200
12 Jan 16	A	150	150
11 Jan 16	B	200
12 Jan 16	B	250
13 Jan 16	B	100	100

[sunny_talwar · Accepted solution]

Try this:

SET DateFormat='DD MMM YY';

Table:

LOAD Date,

     Product,

     Value

FROM

[https://community.qlik.com/thread/209896]

(html, codepage is 1252, embedded labels, table is @1);

Join (Table)

LOAD Product,

  Date(Max(Date)) as Date,

  FirstSortedValue(Value, -Date) as [Latest Product Value]

Resident Table

Group By Product;

[Chanty4u]

dim:Product

Expression: =FirstSortedValue(Value, -Date)

[Kushal_Chawda]

one more way

If your Date is in Text Format then use Date# like below else no need.

Data:

Load date(date#(Date,'DD MMM YY'),'DD MMM YY') as Date

     Product,

     Value

From Table;

left join (Data)

Load Date(max(Date),'DD MMM YY') as Date,

     Product,

     1 as LatestProductValueFlag

Resident Data

Group By Product;

left join (Data)

Load Product,sum(Value) as [Latest Product Value]

Resident Data

where LatestProductValueFlag=1

Group By Product;

[Chanty4u]

one more sample