5 Replies Latest reply: May 11, 2018 3:59 AM by Sasidhar Parupudi RSS

    load order by not workding

    Bassam Frem

      Hello friend

       

      The below script is not ordering the table according to agreement_id and collateral_id.

      The purpose is to calculate the "agreement_amortization_schedule_calc

      The calculated result is not correct, and it seems the reasons is that the Order By is not working.

      What am i missing here ?

       

      Collaterals:

      LOAD

          num#(collateral_id) as collateral_id,

          num#(collateral_reference) as collateral_reference,

          initial_amount as collateral_initial_amount,

          original_loan_amount_calc,

           num#(agreement_id) as agreement_id

      FROM [lib://QVD First Layer (bsec-sa_bfrem_bsec)/Collaterals.qvd](qvd);

       

      Left Join (Collaterals)

      data_temp:

      Load

           agreement_id,

          collateral_id,

          if(agreement_id<>peek(agreement_id),original_loan_amount_calc,Peek(agreement_amortization_schedcule_calc))

          -collateral_initial_amount as agreement_amortization_schedcule_calc

      Resident Collaterals

      Order by agreement_id, collateral_id asc;

       

       

       

      Thanks

        • Re: load order by not workding
          Akshaya Aditya

          Hi Bassam,

           

          I will suggest, first do the left join and then do the order by.

           

          In your script, you are ordering the data_temp and joining it with Collaterals table.

           

          Rather, I would like to suggest like below

           

          Collaterals_Temp:

          LOAD

              num#(collateral_id) as collateral_id,

              num#(collateral_reference) as collateral_reference,

              initial_amount as collateral_initial_amount,

              original_loan_amount_calc,

               num#(agreement_id) as agreement_id

          FROM [lib://QVD First Layer (bsec-sa_bfrem_bsec)/Collaterals.qvd](qvd);

           

          Left Join (Collaterals_Temp)

          Load

               agreement_id,

              collateral_id,

              if(agreement_id<>peek(agreement_id),original_loan_amount_calc,Peek(agreement_amortization_schedcule_calc))

              -collateral_initial_amount as agreement_amortization_schedcule_calc

          Resident Collaterals

           

           

          NoConcatenate

          Collaterals

          Load  *

          resident Collaterals_Temp order by agreement_id, collateral_id asc;


          Drop table Collaterals_Temp ;


          Regards,

          Akshaya


          PS - If you find response helpful or correct, Please mark it.

            • Re: load order by not workding
              Bassam Frem

              Hello Akshaya

               

              The calculation formula for agreement_amortization_schedule_calc has to be made after the ordering command (otherwise the result would not be correct)

              What would you suggest in this case ?

               

               

              Best regards

              Bassam

                • Re: load order by not workding
                  Akshaya Aditya

                  Hi Bassam,

                   

                  I would like to suggest the below script. This will work in your case

                   

                  Collaterals_Temp:

                  LOAD

                      num#(collateral_id) as collateral_id,

                      num#(collateral_reference) as collateral_reference,

                      initial_amount as collateral_initial_amount,

                      original_loan_amount_calc,

                       num#(agreement_id) as agreement_id

                   

                  FROM [lib://QVD First Layer (bsec-sa_bfrem_bsec)/Collaterals.qvd](qvd) order by agreement_id, collateral_id asc;

                   

                   

                   

                  NoConcatenate

                  Collaterals:

                   

                  Load *

                  , if(agreement_id<>peek(agreement_id),original_loan_amount_calc,Peek(agreement_amortization_schedcule_calc))

                      -collateral_initial_amount as agreement_amortization_schedcule_calc

                  resident Collaterals_Temp order by agreement_id,collateral_id asc;

                   

                  Drop table Collaterals_Temp;

                  • Re: load order by not workding
                    David Forest

                    Do as Akshaya says, just move agreement field to last table;

                     

                    NoConcatenate

                    Collaterals

                    Load  *,

                    f(agreement_id<>peek(agreement_id),original_loan_amount_calc,Peek(agreement_amortization_schedcule_calc))

                        -collateral_initial_amount as agreement_amortization_schedcule_calc

                    resident Collaterals_Temp order by agreement_id, collateral_id asc

                • Re: load order by not workding
                  Sasidhar Parupudi

                  I think it may be that your calculation have an issue. Please load the table with order by clause alone to see if the ordering works as expected.

                  Can you see if both the id's are actually numbers?

                  Collaterals:

                  LOAD

                      num#(collateral_id) as collateral_id,

                      num#(collateral_reference) as collateral_reference,

                      initial_amount as collateral_initial_amount,

                      original_loan_amount_calc,

                       num#(agreement_id) as agreement_id

                  FROM [lib://QVD First Layer (bsec-sa_bfrem_bsec)/Collaterals.qvd](qvd);

                   

                  //Left Join (Collaterals)

                  data_temp:

                  Noconcatenate  Load

                       agreement_id,

                      collateral_id,

                    //  if(agreement_id<>peek(agreement_id),original_loan_amount_calc,Peek(agreement_amortization_schedcule_calc))

                      -collateral_initial_amount as agreement_amortization_schedcule_calc

                  Resident Collaterals

                  Order by agreement_id, collateral_id asc;