Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Not applicable

Value based on max date in script

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

  

DateProductValue
10 Jan 16A100
11 Jan 16A200
12 Jan 16A150
11 Jan 16B200
12 Jan 16B250
13 Jan 16B100

I need the Output like below

DateProductValueLatest Product Value
10 Jan 16A100
11 Jan 16A200
12 Jan 16A150150
11 Jan 16B200
12 Jan 16B250
13 Jan 16B100100
1 Solution

Accepted Solutions

Re: Value based on max date in script

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;


Capture.PNG

View solution in original post

4 Replies

Re: Value based on max date in script

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;


Capture.PNG

View solution in original post

Chanty4u
Esteemed Contributor III

Re: Value based on max date in script

dim:Product

Expression: =FirstSortedValue(Value, -Date)

Re: Value based on max date in script

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
Esteemed Contributor III

Re: Value based on max date in script

one more sample