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

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

    Sergio Rey

      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?
          Sergey Pokasov

          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;

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

              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?
                  Sergey Pokasov

                  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

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

                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.