topic Re: Using Peek for the Next Row in Script in QlikView

Using Peek for the Next Row in Script

eduardo_palacios — Fri, 12 Feb 2016 17:48:05 GMT

Hello everybody,

Can someone tell me why this isn't working?

temp:

LOAD

RowNo() as CurrentRow,

RowNo() + 1 as NextRow

Resident

OldTable

Order By

ContractNumber, refDate

;

drop table OldTable;

NewTable:

LOAD

Peek('Date', num#(NextRow)) AS NextRowDate

Resident

temp

Order By

ContractNumber, refDate

;

DROP TABLE temp;

I need to input on the current row a field telling me what is on a specific field on the next row. The 'NextRowDate' is showing a null value instead of the value of the field Date on the next Row.

Need help!

Thanks in advance.

Re: Using Peek for the Next Row in Script

sunny_talwar — Fri, 12 Feb 2016 18:00:23 GMT

I think to look into next row, I would do a sort in descending order and then use the regular way of doing the peek.

Re: Using Peek for the Next Row in Script

maxgro — Fri, 12 Feb 2016 20:48:05 GMT

// test data

load

RecNo() as CurrentRow,

date(makedate(2014)+floor(rand()*1000)) as RefDate,

floor(rand()*10) as ContractNumber

AutoGenerate 100;

// add next row e date

Left join (X)

load

ContractNumber,

CurrentRow,

previous(CurrentRow) as NextRow,

previous(RefDate) as NextRefDate

Resident X

Order By ContractNumber, RefDate desc, CurrentRow;

Re: Using Peek for the Next Row in Script

Oleg_Troyansky — Fri, 12 Feb 2016 20:56:49 GMT

I think the reason it's no working is because peek() can work with either one parameter or three parameters, but not with two:

- Peek(Field) will give you the previous value of the Field in the current table

- Peek(Field, Row, Table) will give you the value of the Field from the specified Row in the specified Table.

In your example, you didn't specify the table, and that is likely the reason of the problem.

Having said that, I agree that the more intuitive solution is to sort the table in the descending order and retrieve the previous value, instead of the next value.

cheers,

Oleg Troyansky

Upgrade your Qlik skills at the Masters Summit for Qlik - coming soon to Milan, Italy!