topic Re: How to load latest date from multiple dates in QlikView

How to load latest date from multiple dates

Sun, 20 Jan 2013 18:04:20 GMT

Hi,

I have one question about loading dates from database.

For Example I have table as below (Customer prices).

ID DATE PRICE

1 3.2.2013 50,4

1 31.12.2012 60

1 5.9.2011 65

2 1.12.2012 2

2 24.9.2012 1,45

2 5.4.2012 3

3 15.3.2013 21,2

3 1.11.2012 19,3

3 3.9.2011 20,4

Now I would like only get infromation about current price which is active to customer based on current date (Result shoud be as example below). Just to reminder current date is 20.1.2013 when i wrote this post.

ID DATE PRICE

1 31.12.2012 60

2 1.12.2012 2

3 1.11.2012 19,3

What is the best way to do this?

Tried Max(Date) function,

FirstSortedValue with Group By condition

Thank you in advance!

Re: How to load latest date from multiple dates

swuehl — Sun, 20 Jan 2013 18:29:16 GMT

FirstSortedValue() with a group by should do the trick (and limiting the records to past and current dates).

INPUT:

LOAD ID,

Date(max(DATE)) as DATE,

FirstSortedValue(PRICE,-DATE) as ValidPrice

where DATE <= today() group by ID;

LOAD * INLINE [

ID DATE PRICE

1 3.2.2013 '50,4'

1 31.12.2012 60

1 5.9.2011 65

2 1.12.2012 2

2 24.9.2012 '1,45'

2 5.4.2012 3

3 15.3.2013 21,2

3 1.11.2012 '19,3'

3 3.9.2011 '20,4'

] (delimiter is ' ');

Re: How to load latest date from multiple dates

Mon, 21 Jan 2013 10:37:45 GMT

see attachement..

Re: How to load latest date from multiple dates

Tue, 22 Jan 2013 20:50:00 GMT

Thank you! Works like charm!

Re: How to load latest date from multiple dates

Tue, 22 Jan 2013 20:50:35 GMT

Thank you for the example! Was very helpful!