topic Re: using Previous with criteria in QlikView

using Previous with criteria

samuel_brierley — Fri, 31 Jul 2015 09:57:32 GMT

Hi all,

is it possible to build criteria into the previous/peek function?

i.e

Field 1, Field 2, Field 3, Calculated Field

A,Z,1,-

B,X,2,-

C,Y,3,-

D,W,4,-

A,Z,5,1

the criteria for the calculated field would be to look upwards and return the value of field 3 where field 1 and 2 are the same.

Thanks guys.

Re: using Previous with criteria

antoniotiman — Fri, 31 Jul 2015 10:02:58 GMT

You use Lookup() function

See

Lookup function

Re: using Previous with criteria

Colin-Albert — Fri, 31 Jul 2015 10:03:45 GMT

You can use something like this. But in your example data Field 1 and Field2 differ on every row.

IF(Field1 = Field2, Peek(Field3), 'No Match')) as CalculatedField

Re: using Previous with criteria

sunilkumarqv — Fri, 31 Jul 2015 10:04:30 GMT

I'm not sure you expecting

something like this

IF(field 1=field 2,Previous(field 3),field 3)

Re: using Previous with criteria

maxgro — Fri, 31 Jul 2015 10:05:08 GMT

like this?

if yes this is the script

source:

load * inline [

Field 1, Field 2, Field 3, Calculated Field

A,Z,1,-

B,X,2,-

C,Y,3,-

D,W,4,-

A,Z,5,1

];

final:

NoConcatenate

load

[Field 1], [Field 2], [Field 3],

if(peek([Field 1])=[Field 1] and peek([Field 2])=[Field 2], Peek([Field 3]), [Field 3]) as [Calculated Field]

Resident source

order by [Field 1], [Field 2];

DROP Table source;

Re: using Previous with criteria

samuel_brierley — Fri, 31 Jul 2015 10:10:16 GMT

Yes exactly like that!!!

one minor change is it possible in your pic rows 3-5 you return 'X' instead of its own value?

if not dont worry.

thanks!!!

Re: using Previous with criteria

maxgro — Fri, 31 Jul 2015 10:48:01 GMT

source:

load *, rowno() as id inline [

Field 1, Field 2, Field 3, Calculated Field

A,Z,1,-

B,X,2,-

C,Y,3,-

D,W,4,-

A,Z,5,1

];

Left join (source)

load [Field 1], [Field 2], count([Field 1]) as cnt Resident source

group by [Field 1], [Field 2];

final:

NoConcatenate

load

[Field 1], [Field 2], [Field 3],

if(peek([Field 1])=[Field 1] and peek([Field 2])=[Field 2] or cnt>1, alt(peek([Field 3]), [Field 3]), 'x') as [Calculated Field]

Resident source

order by [Field 1], [Field 2], id;

DROP Table source;