topic Re: Latest 4 last 4 values in QlikView

Latest 4 last 4 values

Thu, 07 May 2015 18:56:57 GMT

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

Re: Latest 4 last 4 values

ramoncova06 — Thu, 07 May 2015 19:04:48 GMT

check this thread it has several options for top 4

https://www.qlikcommunity.com/thread/157792

Re: Latest 4 last 4 values

Anonymous — Thu, 07 May 2015 19:17:51 GMT

=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)

Re: Latest 4 last 4 values

Anonymous — Thu, 07 May 2015 19:18:34 GMT

See attached file

Re: Latest 4 last 4 values

Thu, 07 May 2015 19:22:10 GMT

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?

Susvith

Re: Latest 4 last 4 values

Thu, 07 May 2015 19:31:02 GMT

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

Re: Latest 4 last 4 values

ramoncova06 — Thu, 07 May 2015 19:34:25 GMT

what Susvith was refering to is where do you need this information ?

in a table ? in a listbox ? as part of your data set ?

Re: Latest 4 last 4 values

Thu, 07 May 2015 19:38:25 GMT

I need to have separate 4 values .

I have to apply other calculation during the load based on the 4 EDT1 date values.

Re: Latest 4 last 4 values

ramoncova06 — Thu, 07 May 2015 19:47:07 GMT

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;

Re: Latest 4 last 4 values

maxgro — Thu, 07 May 2015 20:00:26 GMT

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;

Re: Latest 4 last 4 values

Thu, 07 May 2015 20:25:26 GMT

Thank u Massimo Grossi.

Re: Latest 4 last 4 values

Anonymous — Thu, 07 May 2015 20:28:36 GMT

Re: Latest 4 last 4 values

MarcoWedel — Thu, 07 May 2015 21:25:52 GMT

If you also need the C1 values corresponding to the last 4 EDT1 values:

firstsortedvalue(C1, -EDT1, 1)

firstsortedvalue(C1, -EDT1, 2)

firstsortedvalue(C1, -EDT1, 3)

firstsortedvalue(C1, -EDT1, 4)

regards

Marco