topic Re: How to look backwards in a load statement based on a condition? in QlikView

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

sergiorey — Mon, 27 Apr 2015 23:36:25 GMT

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.

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

pokassov — Tue, 28 Apr 2015 06:18:27 GMT

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

Group by Cond1, Cond2;

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

Left Join (t1)

LOAD

Resident t0;

DROP Table t0;

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

sergiorey — Tue, 28 Apr 2015 14:50:14 GMT

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.

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

pokassov — Tue, 28 Apr 2015 15:46:11 GMT

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

where ID>ID%

Group by No;

DROP Table t2;

Left Join (t1)

LOAD

Resident t4;

DROP Table t4;

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

Tue, 28 Apr 2015 19:36:35 GMT

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.

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

sergiorey — Wed, 29 Apr 2015 15:23:43 GMT

It really works for me!

Thank you very much Sergey!

I really appreciate your help!

Kind Regards,

Sergio.