Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
atsushi_saijo
Creator II
Creator 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?

1 Solution

Accepted Solutions
Not applicable

Hi Atsushi,

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

Should work for you

Hopet hat helps

Joe

View solution in original post

7 Replies
Not applicable

Hi Atsushi,

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

Should work for you

Hopet hat helps

Joe

senpradip007
Specialist III
Specialist III

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;

Gysbert_Wassenaar

LOAD

     Type,

     Seq,

     Value,

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

FROM ...


talk is cheap, supply exceeds demand
atsushi_saijo
Creator II
Creator II
Author

Thank you for quick answer. It works.

atsushi_saijo
Creator II
Creator II
Author

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

atsushi_saijo
Creator II
Creator II
Author

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

Not applicable

no problem, glad to help