Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
There is a table
Table1
callsign | priority | start date | completion date | values |
101 | a medical office | 26.05.2014 | 31.12.2020 | 1 |
101 | a medical office | 15.05.2015 | 16.05.2015 | 1 |
101 | back glass | 26.05.2014 | 31.12.2020 | 1 |
101 | back glass | 18.11.2014 | 31.12.2020 | 1 |
101 | brand | 26.05.2014 | 31.12.2020 | 1 |
101 | brand | 18.11.2014 | 31.12.2020 | 1 |
101 | resolution | 26.05.2014 | 31.12.2020 | 1 |
101 | star | 26.05.2014 | 31.12.2020 | 1 |
need formula to obtain the last sample value in the field "Stat date"
Table2
callsign | priority | start date | completion date | values |
101 | a medical office | 15.05.2015 | 16.05.2015 | 1 |
101 | back glass | 18.11.2014 | 31.12.2020 | 1 |
101 | brand | 18.11.2014 | 31.12.2020 | 1 |
101 | resolution | 26.05.2014 | 31.12.2020 | 1 |
101 | star | 26.05.2014 | 31.12.2020 | 1 |
thank you for your attention
First of all, make sure your dates are proper dates instead of strings, then you can use date functions and compare which date is older.
Then you can use in your expression like
if([start date]=Max(TOTAL <priority> [start date]),values)
You can see the result for the current situation by
if([start date]=MaxString(TOTAL <priority> [start date]),values)
First of all, make sure your dates are proper dates instead of strings, then you can use date functions and compare which date is older.
Then you can use in your expression like
if([start date]=Max(TOTAL <priority> [start date]),values)
You can see the result for the current situation by
if([start date]=MaxString(TOTAL <priority> [start date]),values)
For the date load you can use this
Date(Date#([start date],'DD.MM.YYYY')) as NewDate
Hi,
Try:
TMP1:
LOAD
*
Inline [
callsign, priority, start date, completion date, values
101, a medical office, 26.05.2014, 31.12.2020, 1
101, a medical office, 15.05.2015, 16.05.2015, 1
101, back glass, 26.05.2014, 31.12.2020, 1
101, back glass, 18.11.2014, 31.12.2020, 1
101, brand, 26.05.2014, 31.12.2020, 1
101, brand, 18.11.2014, 31.12.2020, 1
101, resolution, 26.05.2014, 31.12.2020, 1
101, star, 26.05.2014, 31.12.2020, 1
];
Right Join (TMP1)
LOAD
priority,
LastValue([start date]) as "start date"
Resident TMP1
Group by priority;
thank you for the quick reply, what you need, best regards
Thank you, it needed the formula.
Thanks for the reply on the task