Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
sergiorey
New Contributor III

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.

Tags (1)
1 Solution

Accepted Solutions
pokassov
Valued Contributor

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:

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

5 Replies
pokassov
Valued Contributor

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

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
New Contributor III

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

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
Valued Contributor

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:

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

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

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
New Contributor III

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

It really works for me!

Thank you very much Sergey!

I really appreciate your help!

Kind Regards,

Sergio.

Community Browser