5 Replies Latest reply: Apr 29, 2015 11:23 AM by Sergio Rey

# How to look backwards in a load statement based on a condition?

Hi all,

I need to look backwards in a load statement so as to get the row that satisfy a condition.

Example:

No      Name      Cond1      Cond2

----       ----           ----           ----

101      fsdfs         3              2 --> Goal row

102      klhhj         3              3

103      asddd       3              4

104      qwert        3              3 --> Current row

105      poier         0              4

106      hjkll          3               2

If current row is the correspondig to No=104, I want to get the No for the previos loaded row that has Cond1=3 (Current value for Cond1)
and Cond2=2 (Current value for Cond2 - 1).

The finall result should be row No=101.

Any help will be much appreciated.

Thanks!

Sergio.

• ###### Re: How to look backwards in a load statement based on a condition?

Hello!

t1:

No,Name,Cond1,Cond2

101,fsdfs,3,2

102,klhhj,3,3

103,asddd,3,4

104,qwert,3,3

105,poier,0,4

106,hjkll,3,2

];

//-- calculation your conditions and finding first No for condition

t0:

Cond1 as Cond1,

Cond2+1 as Cond2,

min(No) as GoalNo

Resident

t1

Group by Cond1, Cond2;

//-- join result for original table. field GoalNo

Left Join (t1)

*

Resident t0;

DROP Table t0;

• ###### Re: How to look backwards in a load statement based on a condition?

I don't need the min(No) that satisfy the condition. I need to get the previously loaded row that satisfy that condition, the nearest row looking backwards.

Example:

No      Name      Cond1      Cond2

----       ----           ----           ----

101      fsdfs         3              2  --> This is the min(No) that satisfy the condition but is not the Goal row.

102      klhhj         3              3

103     abab          3              2  --> Goal row

104     dumm        0              5

105      asddd       3              4

106      qwert        3              3  --> Current row

107      poier         0              4

108      hjkll          3               2

Any ideas?

Thanks again Sergey.

• ###### Re: How to look backwards in a load statement based on a condition?

Well.

I don't expect high marks for elegant solution....

However it works. I multiplyed your original table and the "condition" table.

After that I found the nearest row with condition checking that it less then my current No.

//original table

t1:

No,Name,Cond1,Cond2

101,fsdfs,3,2

102,klhhj,3,3

103,abab,3,2

104,dumm,0,5

105,asddd,3,4

106,qwert,3,3

107,poier,0,4

108,hjkll,3,2

];

//key fields with rownum

t2:

NoConcatenate

No,

Cond1,

Cond2,

RowNo() as ID

Resident t1

Order By No;

//condition

t3:

NoConcatenate

Cond1 as Cond1,

Cond2+1 as Cond2,

No as No%,

ID as ID%

Resident t2;

//multiply 2 tables

Left Join (t2)

*

Resident t3;

DROP Table t3;

// select previous condition row

t4:

NoConcatenate

No,

FirstSortedValue(No%,-ID%) as GoalNo

Resident

t2

where ID>ID%

Group by No;

DROP Table t2;

Left Join (t1)

*

Resident t4;

DROP Table t4;

• ###### Re: How to look backwards in a load statement based on a condition?

It really works for me!

Thank you very much Sergey!