topic Where Statement in QlikView

Where Statement

alec1982 — Wed, 20 Nov 2013 18:57:44 GMT

Hi,

I am trying to do where statement as follow but it I am getting error.

Load ID

Where ID>LastValue(ID)-2;

Anybody can help?

Thxs

Re: Where Statement

alec1982 — Thu, 21 Nov 2013 18:04:46 GMT

Wish someone can help on this one

Thxs,

Alec

Re: Where Statement

Anonymous — Thu, 21 Nov 2013 18:10:07 GMT

Badr

Try

Where ID>peek(ID)-2;

If I have misunderstood what you wish to do then please explain your requirement.

Best Regards, Bill Markham

Re: Where Statement

danielact — Thu, 21 Nov 2013 19:13:35 GMT

I believe LastValue needs to have a Group clause in the load script.

Bill was correct except for one thing - you need quotes in the peek function. It would be this:

Where ID>peek('ID')-2;

Re: Where Statement

alec1982 — Thu, 21 Nov 2013 19:47:10 GMT

Hi Guys,

Thanks for the help but I am getting no data as result.

My result table should look like

Any thoughts?

Re: Re: Where Statement

Anonymous — Thu, 21 Nov 2013 20:04:44 GMT

Badr

I hope understand what you are after now, try this :

Where ID > previous(previous(ID))

I tested it with this :

TempTable:

LOAD * INLINE [

];

noconcatenate

Table:

Load

resident TempTable

Where ID > previous(previous(ID))

;

drop table TempTable ;

Best Regards, Bill

Re: Where Statement

MayilVahanan — Thu, 21 Nov 2013 20:05:35 GMT

Try like this

Test1:

Load * Inline

[

];

LET vMax = peek('ID', RowNo(TOTAL)-1, 'Test1');

Test:

NoConcatenate

Load * Resident Test1 where ID > '$(vMax)'-2;

Drop Table Test1;

Re: Where Statement

alec1982 — Thu, 21 Nov 2013 20:13:21 GMT

Hi,

this returned

Thxs,

Alec

Re: Where Statement

alec1982 — Thu, 21 Nov 2013 20:16:27 GMT

Thank you!