Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
How to get last 4 EDT1 field values from this list below
C1 | EDT1 |
50083633 | 22/10/2012 |
50083633 | 30/01/2013 |
50083633 | 16/04/2013 |
50083633 | 11/07/2013 |
50083633 | 29/11/2013 |
50083633 | 13/12/2013 |
50083633 | 02/06/2014 |
50083633 | 01/07/2014 |
50083633 | 02/07/2014 |
50083633 | 08/10/2014 |
50083633 | 27/01/2015 |
50083633 | 01/04/2015 |
50083633 | 04/05/2015 |
50083633 | 06/05/2015 |
check this thread it has several options for top 4
=Subfield(Concat(Distinct EDT1, ';'), ';', count(DISTINCT EDT1))
&'; '&
Subfield(Concat(Distinct EDT1, ';'), ';', count(DISTINCT EDT1)-1)
&'; '&
Subfield(Concat(Distinct EDT1, ';'), ';', count(DISTINCT EDT1)-2)
&'; '&
Subfield(Concat(Distinct EDT1, ';'), ';', count(DISTINCT EDT1)-3)
See attached file
Hi a d,
You can use peek function to get last values.
But can you also be clear, would you like to show these 4 values together or separately and where?
BR
Susvith
Thanks-But it didn't work.
I am expecting the results the below values only.(Peek will provide only 4 th position of the value)
50083633 | 27/01/2015 |
50083633 | 01/04/2015 |
50083633 | 04/05/2015 |
50083633 | 06/05/2015 |
what Susvith was refering to is where do you need this information ?
in a table ? in a listbox ? as part of your data set ?
I need to have separate 4 values .
I have to apply other calculation during the load based on the 4 EDT1 date values.
I believe you can do this with Peek but don't remember how at this time , but you can do something like this
temp:
load * inline
[C1, EDT1
50083633, 22/10/2012
50083633, 30/01/2013
50083633, 16/04/2013
50083633, 11/07/2013
50083633, 29/11/2013
50083633, 13/12/2013
50083633, 02/06/2014
50083633, 01/07/2014
50083633, 02/07/2014
50083633, 08/10/2014
50083633, 27/01/2015
50083633, 01/04/2015
50083633, 04/05/2015
50083633, 06/05/2015];
Max4:
load
max(EDT1,4) as Max4EDT1
resident temp;
Max4EDT1:
Load
C1,
date(EDT1) as Max4EDT1
resident temp
where EDT1 >= fieldvalue('Max4EDT1',1);
drop tables Max4, temp;
temp:
load * inline
[C1, EDT1
50083633, 22/10/2012
50083633, 30/01/2013
50083633, 16/04/2013
50083633, 11/07/2013
50083633, 29/11/2013
50083633, 13/12/2013
50083633, 02/06/2014
50083633, 01/07/2014
50083633, 02/07/2014
50083633, 08/10/2014
50083633, 27/01/2015
50083633, 01/04/2015
50083633, 04/05/2015
50083633, 06/05/2015];
final: NoConcatenate first 4
load * Resident temp order by EDT1 desc;
DROP Table temp;