5 Replies Latest reply: Apr 21, 2011 10:53 AM by Tom Haynes RSS

    Help needed with Inter Record Function in load script

      I am trying (in vain) to create a derived field, as part of the load process, which contains the '0' or '1' value (which is subsequently used as a sum function in the chart expression)

       

      The following table lists two trades that were created on the 1/2/2011. Both trades had amendments on the 2/2/2011 and one of the trades was closed on the 3/2/2011. The objective of the exercise is to determine how many of the trades are still Open.

       

      This is how the data looks: (Table name for the purpose of this explanation = tblTest_OPEN_trades)

       

      Trans

      Action

      _id

      main_

      trade

      _ref

      Trans

      action

      _status

      Trade_

      created

      _on

      Updated_

      Trans

      action

      _received

      _on

      16

      TER - 10

      Open

      01-Feb-11

      01-Feb-11

      21

      TER - 10

      Open

      01-Feb-11

      02-Feb-11

      15

      XYZ - 10

      Open

      01-Feb-11

      01-Feb-11

      20

      XYZ - 10

      Open

      01-Feb-11

      02-Feb-11

      25

      XYZ - 10

      Closed

      01-Feb-11

      03-Feb-11



       

      And this is how I want the result to look: (once created, the data in the table below is saved as a QVD)

       

      Trans

      Action

      _id

      main_

      trade

      _ref

      Trans

      action

      _status

      Trade_

      created

      _on

      Updated_

      Trans

      action

      _received

      _on

      qv_

      open

      _DIST

      16

      TER - 10

      Open

      01-Feb-11

      01-Feb-11

      0

      21

      TER - 10

      Open

      01-Feb-11

      02-Feb-11

      1

      15

      XYZ - 10

      Open

      01-Feb-11

      01-Feb-11

      0

      20

      XYZ - 10

      Open

      01-Feb-11

      02-Feb-11

      0

      25

      XYZ - 10

      Closed

      01-Feb-11

      03-Feb-11

      0



       

      So, GROUPING on [main_trade_ref), if MAX([Updated_transaction_received_on]=Closed, set a derived field called [qv_open_DIST] for all transactions to '0', OTHERWISE set [qv_open_DIST] for the MAX([Updated_transaction_received_on] to '1' (all other iterations for the trade should be set to '0')

       

      Once I have the required value either '0' or'1' in the [qv_open_DIST] field, I simply create a chart and add sum([qv_open_DIST]) as the expression.

       

      This is the code so far:

      Xtrades_Current:

      SQL SELECT *

      FROM `tblTest_OPEN_trades`; // This refers to the initial table

       

      LEFT JOIN (Xtrades_Current)

      Load transaction_id,

      main_trade_ref,

      transaction_status,

      if(peek('main_trade_ref')=main_trade_ref,0,1) as qv_open_DIST

       

      resident Xtrades_Current

      order by main_trade_ref, updated_transaction_received_on DESC;

       

      Can anybody help?

       

       

        • Help needed with Inter Record Function in load script
          Ralf Becher

          Hi,

          I think this is the wrong approach. It is more easy to aggregate the amount of open trades after the load like this:

           

          tblAggr:
          LOAD count(DISTINCT main_trade_ref) as cnt_trades
          Resident tblTest_Open_Trades;
          LOAD count(DISTINCT main_trade_ref) as cnt_trades
          Resident tblTest_Open_Trade
          Where Transaction_status='Closed';
          SET vCntOpen = peek('cnt_trades', 0, 'tblAggr') - peek('cnt_trades', 1, 'tblAggr');
          Drop table tblAggr;


          Or the other way around: LEFT JOIN on closed trades with value 1 and than calculate

          count(DISTINCTmain_trade_ref) - sum(qv_closed_DIST)

          - Ralf

            • Help needed with Inter Record Function in load script

              Thanks Ralf,

              I had the added complication of having to resolve this issue by adding/blending into an existing part in the load file script. After further testing, I think I have figured out the solution, as below, I now need to apply this against 61million rows of data and check for anomolies. Thank you very much for taking the time to assist.

              Xtrades_Current:
              SQL SELECT *
              FROM `tblTest_OPEN_trades`;

               


              Closed_trades:
              Load main_trade_ref as closed_main_trade_ref;

              SQL SELECT *
              FROM `tblTest_OPEN_trades`
              where transaction_status='Closed';

              LEFT JOIN (Xtrades_Current)
              load transaction_id,
              main_trade_ref,

              if(exists(closed_main_trade_ref,main_trade_ref),0,if(peek('main_trade_ref')=main_trade_ref,0,1)) as qv_open_DIST,

              resident Xtrades_Current
              order by main_trade_ref, updated_transaction_received_on DESC;



                • Help needed with Inter Record Function in load script
                  Ralf Becher

                  I would suggest not to load twice (the 'closed') against 61 million rows. Instead you can load resident..

                  - Ralf

                  • Help needed with Inter Record Function in load script
                    Rakesh Mehta

                    Or you may store all the records in a temporary QVD file, and then load from it. Something like this:

                    Xtrades_Current:
                    SQL SELECT *
                    FROM `tblTest_OPEN_trades`;

                    STORE Xtrades_Current into Temp.qvd;

                    tmp:
                    LOAD * INLINE [
                    transaction_status
                    Closed
                    ];

                    Closed_trades:
                    Load main_trade_ref as closed_main_trade_ref
                    FROM Temp.qvd (qvd)
                    WHERE Exists (transaction_status)

                    DROP Table tmp;

                    After this you may drop that temporary QVD file as well.

                      • Help needed with Inter Record Function in load script

                        Thanks Ralf & Rakesh,

                        I have taken the advice from both of you on board and I believe the following code, reflects both of your suggestions - would you mind taking a quick look:

                        Xtrades_Current:
                        SQL SELECT *
                        FROM `tblTest_OPEN_trades`;

                        STORE Xtrades_Current into Temp.qvd;

                        Closed_trades:
                        Load main_trade_ref as closed_main_trade_ref

                        FROM TEMP.qvd(qvd)
                        WHERE transaction_status='Closed';


                        LEFT JOIN (Xtrades_Current)
                        load transaction_id,
                        main_trade_ref,


                        if(exists(closed_main_trade_ref,main_trade_ref),0,if(peek('main_trade_ref')=main_trade_ref,0,1)) as qv_open_DIST,

                        resident Xtrades_Current
                        order by main_trade_ref, updated_transaction_received_on DESC;

                        drop table Closed_trades;