topic Re: previous behaviour in QlikView

previous behaviour

Anonymous — Tue, 13 Sep 2016 14:51:13 GMT

Hi experts,

I use the previous function to detect first occurences of data

As I donot get correct result; i made a test. I hope that someone can explain what happens

I would have expected this result with the scipt below (correct when i uncomment the below Expression):

But I get

This is a very short example. So please no advice that I can do it easier, other way etc. I neet to understand previous function.

What I donot understand: I read in first step the first Dim A with Val 100. There is no previous record, so I get 1 for TEST

In next step I read Ver=2. I get two records, one with Val 120 and one with val 300. For the first record with Val=120 I would expect 1 as result for TEST

as there is no previous record.

Help description: In the first record of an internal table the function will return NULL

Does Qlikview remembers that Dim A was already loaded in first part of Loop? Or even does Qlikview looks at origin (Inline) Table and detects that

Dim A with Val 100 was first, and no change for DIM A, Ver 2 Val 120 in second part of Loop if I only use previous(Dim)??

I have a small Inline table

TEST:
load * inline [
Dim, Ver, Val
A, 1, 100
B, 1, 200
A, 2, 120
B, 2, 220
A, 2, 300
B, 2, 310
];

What I want as result:

In a Loop I load the Versions to test if the record is new.

for x=1 to 2

tmpFinal:
load
Dim,
Val,
Ver,
if (previous(Dim)=Dim ,0, 1) as TEST
//if (previous(Dim)=Dim and (Previous(Ver)=Ver) ,0, 1) as TEST
Resident TEST
where Ver='$(x)'
order by Dim, Val;
next;

Final:
NoConcatenate load *
Resident tmpFinal
where TEST=1;

Re: previous behaviour

kaushiknsolanki — Tue, 13 Sep 2016 15:00:06 GMT

Hi,

If you always want a first occurrence then you can use the firstsortedvalue().

Link below will help.

FirstSortedValue()

Regards,

Kaushik Solanki

Re: previous behaviour

sunny_talwar — Tue, 13 Sep 2016 15:06:26 GMT

May not be relevant as you are trying to understand Previous, but Peek seems to solve the issue:

TEST:

LOAD * Inline [

Dim, Ver, Val

A, 1, 100

B, 1, 200

A, 2, 120

B, 2, 220

A, 2, 300

B, 2, 310

];

//What I want as result:

//In a Loop I load the Versions to test if the record is new.

FOR x = 1 to 2

tmpFinal:

LOAD Dim,

Val,

Ver,

If(Peek('Dim') = Dim, 0, 1) as TEST

Resident TEST

Where Ver = '$(x)'

Order By Dim, Val;

NEXT

Final:

NoConcatenate

LOAD *

Resident tmpFinal

Where TEST = 1;

Re: previous behaviour

sunny_talwar — Tue, 13 Sep 2016 15:13:03 GMT

I think Previous function always looked at the rows in the resident table and I am guessing that where statement is getting completely ignored when checking for Previous.

This is a test to see what we get with Previous and Peek

TEST:

LOAD * Inline [

Dim, Ver, Val

A, 1, 100

B, 1, 200

A, 2, 120

B, 2, 220

A, 2, 300

B, 2, 310

];

FOR x = 1 to 2

tmpFinal:

LOAD Dim,

Val,

Ver,

If(Previous(Dim) = Dim, 0, 1) as TEST,

Previous(Dim),

Peek('Dim')

Resident TEST

Where Ver = '$(x)'

Order By Dim, Val;

NEXT

Final:

NoConcatenate

LOAD *

Resident tmpFinal

Where TEST = 1;

Re: previous behaviour

swuehl — Tue, 13 Sep 2016 15:16:32 GMT

I believe Sunny is kind of right, we need to look at the input table when talking about previous() and the input table is a copy of the RESIDENT TEST table ordered by Dim and Val, but without the WHERE clause already applied.

You can see this also when adding a Recno() field:

tmpFinal:

load

Dim,

Val,

Ver,

if (previous(Dim)=Dim ,0, 1) as TEST,

recno() as TEST2

//if (previous(Dim)=Dim and (Previous(Ver)=Ver) ,0, 1) as TEST

Resident TEST

where Ver='$(x)'

order by Dim, Val;

Re: previous behaviour

Anonymous — Tue, 13 Sep 2016 15:27:40 GMT

Thanks Stefan and Sunny for your explanations.

I overtook a Project from a collegue who used the previous function

and as we were loosing data, I tried to understand why (with your explanations now I know).

Now I consider to use another function for our specific purpose.