topic Re: Loop-alike (Take previous value if condition is met) in QlikView

Loop-alike (Take previous value if condition is met)

atsushi_saijo — Fri, 20 Feb 2015 09:00:57 GMT

I have such data, and must make 'Reference' field in such manner:

Add reference field:

If Type = 'A', take Value;
If Type <> 'A', take the nearest Value of earlier sequence;

*table is ordered by Seq ASC.

[Original Data]

Type	Seq	Value
A	1	1
B	2	21
B	3	22
D	4	23
A	5	5
B	6	39
A	7	7
C	8	54

[Must add "Reference" field]

Type	Seq	Value	Ref
A	1	1	1
B	2	21	1
B	3	22	1
B	4	23	1
A	5	5	5
B	6	39	5
A	7	7	7
B	8	54	7

Would you possibly advise how we would achieve such load?

Re: Loop-alike (Take previous value if condition is met)

Fri, 20 Feb 2015 09:06:54 GMT

Hi Atsushi,

If(Type='A',Value,Peek('Ref',-1)) AS Ref

Should work for you

Hopet hat helps

Joe

Re: Loop-alike (Take previous value if condition is met)

senpradip007 — Fri, 20 Feb 2015 09:11:53 GMT

Use this

T1:

LOAD *

Inline [

Type, Seq, Value

A, 1, 1

B, 2, 21

B, 3, 22

D, 4, 23

A, 5, 5

B, 6, 39

A, 7, 7

C, 8, 54

];

T2:

LOAD *, if(Type = 'A', Value, Peek(Ref)) AS Ref

Resident T1;

DROP Table T1;

Re: Loop-alike (Take previous value if condition is met)

Gysbert_Wassenaar — Fri, 20 Feb 2015 09:11:55 GMT

LOAD

Type,

Seq,

Value,

if(Type='A',Value, peek(Ref)) as Ref

FROM ...

Re: Loop-alike (Take previous value if condition is met)

atsushi_saijo — Fri, 20 Feb 2015 09:55:13 GMT

Thank you for quick answer. It works.

Re: Loop-alike (Take previous value if condition is met)

atsushi_saijo — Fri, 20 Feb 2015 09:56:29 GMT

Dear Gysbert, thank you for the fast reply as usual. It works!

Re: Loop-alike (Take previous value if condition is met)

atsushi_saijo — Fri, 20 Feb 2015 09:57:01 GMT

Thank you Joe for fast reply. It is working, and you are the first reply.

Re: Loop-alike (Take previous value if condition is met)

Fri, 20 Feb 2015 10:10:38 GMT

no problem, glad to help