topic Re: ASK.. How to select Last Row Value? in QlikView

ASK.. How to select Last Row Value?

Mon, 30 Aug 2010 05:46:40 GMT

Hello all, need your help plis 🙂

thank you before,

How to select Last Row Value?

this is my case,

plis see the picture below (this is table in SQL)

note NoRek + NoPin is same as account number

i want to get lastrow CollectorID.

in 1 account number have more than 1 collector,

but i just want to get last row collector (which is the uptodate one)

this pic is my Qlikview result,

because there is two collector ID in that account number, my straigth table can't show the collector ID and collector name.

this is my syntax in edit script

plis help..

need urgent..

thank you so much,

best regards!!

ASK.. How to select Last Row Value?

tresB — Mon, 30 Aug 2010 06:19:27 GMT

Hi,

I think you need to group the data with respect to a calculated field 'NoRek' & 'NoPin' '(Concatenated)' which is same as account number. once grouped, then you have to take it as resident and use PEEK function to check last record for each group.

i can try better if it is possible for you to upload a sample application.

Thanks.

tresesco

ASK.. How to select Last Row Value?

Mon, 30 Aug 2010 06:39:47 GMT

hello tresesco,

thanks for your reply,

i have already group norek and nopin,

plis see my last picture,

and what is peek function??

can you give me an example of that? i mean the syntax.

give an example of syntax base on my case, just give me you get/understand base on my case, later i will correct it 😄

thank you so much tresesco

ASK.. How to select Last Row Value?

tresB — Mon, 30 Aug 2010 07:00:13 GMT

say you have grouped table : GPTABLE

Now you can try something like (this is the idea - some modification might require):

load peek(accountnumber) as accountnumber, // previous account number is taken as present

if(peek(accountnumber)<>accountnumber, CollectorID) as LastCollectorID

Resident GPTABLE;

About PEEK function:

peek( fieldname [ , row [ , tablename ] ] )

Returns the contents of the fieldname in the record specified by row in the input table tablename. Data are fetched from the associative QlikView database.

Fieldname must be given as a string (e.g. a quoted literal).

Row must be an integer. 0 denotes the first record, 1 the second and so on. Negative numbers indicate order from the end of the table. -1 denotes the last record read.

If no row is stated, -1 is assumed.

Tablename is a table label without the ending colon. If no tablename is stated, the current table is assumed. If used outside the load statement or referring to another table, the tablename must be included.

Examples:

peek( 'Sales' )
returns the value of Sales in the previous record read ( equivalent to previous( Sales ) ).

peek( 'Sales', 2 )
returns the value of Sales from the third record read from the current input table.

peek( 'Sales', -2 )
returns the value of Sales from the second last record read into the current input table.

peek( 'Sales', 0, Tab1 )
returns the value of Sales from the first record read into the input table labeled Tab1.

Load A, B, numsum( B, peek( 'Bsum' ) ) as Bsum...;
creates an accumulation of B in Bsum.

Re: ASK.. How to select Last Row Value?

Wed, 05 Mar 2014 07:11:10 GMT

hi,

I'm new to qlik view and when I try to retrieve the last record by using peek('field_name',-1) the first 2 rows are retrieved(not the last record is returned).While peek('field_name',0) and peek('field_name',1) are working fine.Please help me out to retrieve the last record only.

Re: Re: ASK.. How to select Last Row Value?

Wed, 05 Mar 2014 07:14:59 GMT

hi Asia

peek( fieldname , -1, tablename )

this will give the last Record of table

Re: ASK.. How to select Last Row Value?

Wed, 05 Mar 2014 07:28:41 GMT

Hi,

I tried the same but peek(field,-1) retrieves the first 2 records only. I have attached my excel data source and the query I did for your reference.

Query:

LOAD s.no,

name,

data,week(data) - week(monthend(addmonths(data,-1))) as Weeknumber,

week(data) as week_data,

AddMonths(data,-1) as add_months,

WeekStart(data) as week_start,

WeekEnd(data) as week_end, week(monthend(addmonths(data,-1))) as mon_end_add_months_week,

week(AddMonths(data,-1)) as week_add_month,Peek(s.no,0) as fst,

Peek(s.no,1) as sec,

Peek(s.no,-1) as thrd

FROM

C:\Users\387805\Documents\date_work.xlsx

(ooxml, embedded labels, table is Sheet1);

Result:

I’m getting first record as ‘fst’,second record as ‘sec’ but ‘thrd’ containing first 2 records.

Regards,

Asia Imrana M

Re: ASK.. How to select Last Row Value?

Wed, 05 Mar 2014 07:54:29 GMT

Hi,

I found the cause for the issue. Actually I had loaded all the things in the single table. That is why I couldn’t get the last record. Now I have used the 2 load statements individually to load the fields and getting the nth record from the already loaded table(Sales) not from the current table (C:\......\date_work.xlsx)

Sales:

LOAD s.no,

name,

data,week(data) - week(monthend(addmonths(data,-1))) as Weeknumber,

week(data) as week_data,

AddMonths(data,-1) as add_months,

WeekStart(data) as week_start,

WeekEnd(data) as week_end, week(monthend(addmonths(data,-1))) as mon_end_add_months_week,

week(AddMonths(data,-1)) as week_add_month

FROM

C:\Users\387805\Documents\date_work.xlsx

(ooxml, embedded labels, table is Sheet1);

sales1:

load Peek(s.no,0,'Sales') as fst,

Peek(s.no,1,'Sales') as sec,

Peek(s.no,-1,'Sales') as thrd Resident Sales;

It works perfect.

Regards,

Asia Sarthar