Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

atsushi_saijo
Contributor II

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

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]

TypeSeqValue
A11
B221
B322
D423
A55
B639
A77
C854

[Must add "Reference" field]

TypeSeqValueRef
A111
B2211
B3221
B4231
A555
B6395
A777
B8547

Would you possibly advise how we would achieve such load?

Tags (1)
1 Solution

Accepted Solutions
Not applicable

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

Hi Atsushi,

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

Should work for you

Hopet hat helps

Joe

7 Replies
Not applicable

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

Hi Atsushi,

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

Should work for you

Hopet hat helps

Joe

senpradip007
Valued Contributor III

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

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;

MVP & Luminary
MVP & Luminary

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

LOAD

     Type,

     Seq,

     Value,

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

FROM ...


talk is cheap, supply exceeds demand
atsushi_saijo
Contributor II

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

Thank you for quick answer. It works.

atsushi_saijo
Contributor II

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

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

atsushi_saijo
Contributor II

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

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

Not applicable

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

no problem, glad to help