Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
---- ---- ---- ----
100 daskd 3 1
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.
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:
LOAD * Inline [
No,Name,Cond1,Cond2
100,daskd,3,1
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
LOAD
No,
Cond1,
Cond2,
RowNo() as ID
Resident t1
Order By No;
//condition
t3:
NoConcatenate
LOAD
Cond1 as Cond1,
Cond2+1 as Cond2,
No as No%,
ID as ID%
Resident t2;
//multiply 2 tables
Left Join (t2)
LOAD
*
Resident t3;
DROP Table t3;
// select previous condition row
t4:
NoConcatenate
LOAD
No,
FirstSortedValue(No%,-ID%) as GoalNo
Resident
t2
where ID>ID%
Group by No;
DROP Table t2;
Left Join (t1)
LOAD
*
Resident t4;
DROP Table t4;
Hello!
//-- your original table
t1:
LOAD * Inline [
No,Name,Cond1,Cond2
100,daskd,3,1
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:
LOAD
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)
LOAD
*
Resident t0;
DROP Table t0;
Thanks for your reply Sergey.
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
---- ---- ---- ----
100 daskd 3 1
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.
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:
LOAD * Inline [
No,Name,Cond1,Cond2
100,daskd,3,1
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
LOAD
No,
Cond1,
Cond2,
RowNo() as ID
Resident t1
Order By No;
//condition
t3:
NoConcatenate
LOAD
Cond1 as Cond1,
Cond2+1 as Cond2,
No as No%,
ID as ID%
Resident t2;
//multiply 2 tables
Left Join (t2)
LOAD
*
Resident t3;
DROP Table t3;
// select previous condition row
t4:
NoConcatenate
LOAD
No,
FirstSortedValue(No%,-ID%) as GoalNo
Resident
t2
where ID>ID%
Group by No;
DROP Table t2;
Left Join (t1)
LOAD
*
Resident t4;
DROP Table t4;
Generally we use Peek pr Previous functions to look back the previous value. But in this case we don't the row no. So it would be case we can join with condition on the table like above.
It really works for me!
Thank you very much Sergey!
I really appreciate your help!
Kind Regards,
Sergio.