Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sergiorey
Partner - Creator
Partner - Creator

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

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

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.

1 Solution

Accepted Solutions
pokassov
Specialist
Specialist

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;

Безымянный.png

View solution in original post

5 Replies
pokassov
Specialist
Specialist

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;

sergiorey
Partner - Creator
Partner - Creator
Author

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.

pokassov
Specialist
Specialist

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;

Безымянный.png

Not applicable

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.

sergiorey
Partner - Creator
Partner - Creator
Author

It really works for me!

Thank you very much Sergey!

I really appreciate your help!

Kind Regards,

Sergio.