topic Re: how to get last record?? in QlikView

how to get last record??

soha1902 — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I am using one excel sheet in my qlikview application. this excel contains data like

(PDH-CSV 4.0) (India Standard Time)(-330)	\\Memory\% Committed Bytes In Use	\\Processor(_Total)\% Processor Time
17:18.2	42.06360428
17:28.2	41.69832799	17.59109493
17:38.2	41.64971858	12.45009383
17:48.2	41.61861649	12.72759386
17:58.2	41.63010643	11.94525163
18:08.2	41.85406134	12.58417061

Now I want to load last loaded record for all three fields in 3 different variables during load time. For example

Values shold be after load:

vTime = 18:08:2

vByte = 41.85406134

vProcessTime = 12.58417061

How I will get this?

Thanks in advance.

Re: how to get last record??

swuehl — Wed, 27 Apr 2016 14:43:10 GMT

Let vTime = Peek('(PDH-CSV 4.0) (India Standard Time)(-330)', -1, 'TABLENAME');

other variables accordingly, changing the field name.

Re: how to get last record??

Anil_Babu_Samineni — Wed, 27 Apr 2016 14:44:31 GMT

Some one discussed by previous : https://community.qlikview.com/thread/68081

Re: how to get last record??

sunny_talwar — Wed, 27 Apr 2016 14:46:21 GMT

Try this:

Table:

LOAD Time#([(PDH-CSV 4.0) (India Standard Time)(-330)], 'mm:ss.f') as [(PDH-CSV 4.0) (India Standard Time)(-330)],

[\\Memory\% Committed Bytes In Use],

[\\Processor(_Total)\% Processor Time]

FROM

[https://community.qlik.com/thread/214755]

(html, codepage is 1252, embedded labels, table is @1);

Max:

LOAD FirstSortedValue([(PDH-CSV 4.0) (India Standard Time)(-330)], -[(PDH-CSV 4.0) (India Standard Time)(-330)]) as MaxTime,

FirstSortedValue([\\Memory\% Committed Bytes In Use], -[(PDH-CSV 4.0) (India Standard Time)(-330)]) as MaxByte,

FirstSortedValue([\\Processor(_Total)\% Processor Time], -[(PDH-CSV 4.0) (India Standard Time)(-330)]) as MaxProcessTime

Resident Table;

LET vTime = Peek('MaxTime', -1, 'Max');

LET vByte = Peek('MaxByte', -1, 'Max');

LET vProcessTime = Peek('MaxProcessTime', -1, 'Max');

DROP Table Max;

Re: how to get last record??

Anonymous — Wed, 27 Apr 2016 14:51:57 GMT

let vTime= peek(fieldname , -1, tablename) ..check how to use peek.

Re: how to get last record??

sunny_talwar — Wed, 27 Apr 2016 14:54:26 GMT

Stefan -

Wouldn't this require the table to be sorted correctly? As it will always pick the last value in the load? Not sure if that is what is required, but I just wanted to confirm

Re: how to get last record??

swuehl — Wed, 27 Apr 2016 14:57:25 GMT

Well, it was asked for 'Now I want to load last loaded record for all three fields in 3 different variables during load time'.

That's what the Peek() functions return.

Re: how to get last record??

sunny_talwar — Wed, 27 Apr 2016 15:00:02 GMT

So it won't check for the sorting, right? Just for my knowledge, OP may be looking for what you have, but I want to make sure I understand Peek()

Re: how to get last record??

swuehl — Wed, 27 Apr 2016 15:05:32 GMT

Right, it won't care if the data is sorted.

It just returns a value from a specified row of your resident table (in this case, the last row).

But the data looks like a performance monitor log, already sorted. Assumingly last record -> latest record.

Re: how to get last record??

sunny_talwar — Wed, 27 Apr 2016 15:07:29 GMT

This one is, but in case it isn't then last one will be picked. That makes sense.

Thanks Stefan