Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everyone,
I have a table with following three field
Item | Value | Date |
---|---|---|
A | 200 | 03/06/2014 |
A | 10 | 01/02/2014 |
B | 45 | 05/08/2013 |
B | 36 | 01/04/2014 |
and my query is to display a table as follow
Item | Value | date |
---|---|---|
A | 200 | 03/06/2014 |
B | 36 | 01/04/2014 |
Thanks in advance
Use Item as dimension, the expression max(Date) to find the maximum date and the expression firstsortedvalue(Value, -Date) to find the value corresponding with the maximum date. Note, Date should be a field with real date values. If the field contains string values then you'll have to create dates from them first in the script: date#(Date, 'MM/DD/YYYY') as Date. Or maybe you need DD/MM/YYYY as date format. I can't tell from your data.
Use Item as dimension, the expression max(Date) to find the maximum date and the expression firstsortedvalue(Value, -Date) to find the value corresponding with the maximum date. Note, Date should be a field with real date values. If the field contains string values then you'll have to create dates from them first in the script: date#(Date, 'MM/DD/YYYY') as Date. Or maybe you need DD/MM/YYYY as date format. I can't tell from your data.
Thank you Gysbert..:)