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

[atsushi_saijo]

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?

[#2 · Accepted solution]

Hi Atsushi,

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

Should work for you

Hopet hat helps

Joe

[senpradip007]

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 ...

[atsushi_saijo]

Thank you for quick answer. It works.

[atsushi_saijo]

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

[atsushi_saijo]

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

[#8]

no problem, glad to help