Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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 ' ');
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 ' ');
HI
see attachement..
Thank you! Works like charm!
Thank you for the example! Was very helpful!