Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Case Portal has moved to Qlik Community! Read the FAQs to start exploring Support resources.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to load latest date from multiple dates

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!

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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 ' ');

View solution in original post

4 Replies
swuehl
MVP
MVP

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 ' ');

View solution in original post

Not applicable
Author

HI

     see attachement..

Not applicable
Author

Thank you! Works like charm!

Not applicable
Author

Thank you for the example! Was very helpful!