topic Re: Value based on max date in script in QlikView

Value based on max date in script

Thu, 17 Mar 2016 09:15:24 GMT

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

Re: Value based on max date in script

sunny_talwar — Thu, 17 Mar 2016 09:19:38 GMT

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;

Re: Value based on max date in script

Chanty4u — Thu, 17 Mar 2016 09:23:10 GMT

dim:Product

Expression: =FirstSortedValue(Value, -Date)

Re: Value based on max date in script

Kushal_Chawda — Thu, 17 Mar 2016 09:23:11 GMT

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;

Re: Value based on max date in script

Chanty4u — Thu, 17 Mar 2016 09:26:18 GMT

one more sample