17 Replies Latest reply: Aug 26, 2016 2:38 AM by Jose Miguel Vilaplana Pascual RSS

    Incremental Reload Issue

    Jeremiah Lee

      //Initial Load to store DB records(Jan2010-Feb2016) in QVD

      TradeTBL:
      LOAD buyerbrokercompanyname,
      Buyeruderid,
      instrument,
      Orderid,
      Price,
      Product,
      quantity,
      sellerbrokercompanyname,
      Selleruserid,
      Status,
      Strip_Name,
      Trade_Date,
      units,
      iscancelled,
      isfromeditor,
      isfrombroker,
      windowid,
      windowname,
      isclear,
      Hubname,
      sellercompanyname,
      Seller,
      Buyer;

      SELECT
      o122616.buyerbrokercompanyname AS buyerbrokercompanyname,
      o122616.buyeruserid AS Buyeruserid,
      o122612.instrument AS instrument,
      o122616.orderid AS Orderid,
      o122616.price AS Price,
      o122610.productname as Product,
      o122616.quantity AS quantity,
      o122616.sellerbrokercompanyname AS sellerbrokercompanyname,
      o122616.selleruserid AS Selleruserid,
      o122616.status AS Status,
      o122611.stripname AS Strip_Name,
      o122616.trade_date AS Trade_Date,
      o122616.units AS units,
      o122616.iscancelled AS iscancelled,
      o122616.isfrombroker AS isfrombroker,
      o122616.isfromeditor AS isfromeditor,
      o130889.windowid AS windowid,
      o130889.windowname AS windowname,
      o122616.isclear AS isclear,
      o196398.hubname as Hubname,
      o122616.sellercompanyname
      || (CASE WHEN o122616.ordertype = 'Offer' THEN '*' ELSE NULL END)
      AS Seller,
      o122616.buyercompanyname
      || (CASE WHEN o122616.ordertype = 'Bid' THEN '*' ELSE NULL END)
      AS Buyer

      FROM ewindow.d_product o122610,
      ewindow.d_hub o196398,
      ewindow.d_strip o122611,
      ewindow.d_window_state o122612,
      ewindow.t_window o130889,
      ewindow.f_transaction o122616

      WHERE (o122610.productid = o122616.productid)
      AND (o130889.windowid = o122612.windowid)
      AND (o122611.stripid = o122616.stripid)
      AND (o196398.hubid = o122616.hubid)
      AND (o122612.window_state_id = o122616.window_state_id)
      AND (o122616.reforderid IS NOT NULL)
      AND (o122616.status = 'consummated')
      AND (o122616.trade_date >='01-Jan-2010')
      AND (o122616.trade_date <='30-Feb-2016')
      ORDER BY o122616.trade_date ASC , o122610.productname ASC;

      STORE TradeTBL into 'C:\Qlikview\Incremental_Load1.qvd';

      // Extract only records from Mar2016 in DB and store in QVD

      TradeTBL:
      LOAD buyerbrokercompanyname,
      Buyeruderid,
      instrument,
      Orderid,
      Price,
      Product,
      quantity,
      sellerbrokercompanyname,
      Selleruserid,
      Status,
      Strip_Name,
      Trade_Date,
      units,
      iscancelled,
      isfromeditor,
      isfrombroker,
      windowid,
      windowname,
      isclear,
      Hubname,
      sellercompanyname,
      Seller,
      Buyer;

      SELECT
      o122616.buyerbrokercompanyname AS buyerbrokercompanyname,
      o122616.buyeruserid AS Buyeruserid,
      o122612.instrument AS instrument,
      o122616.orderid AS Orderid,
      o122616.price AS Price,
      o122610.productname as Product,
      o122616.quantity AS quantity,
      o122616.sellerbrokercompanyname AS sellerbrokercompanyname,
      o122616.selleruserid AS Selleruserid,
      o122616.status AS Status,
      o122611.stripname AS Strip_Name,
      o122616.trade_date AS Trade_Date,
      o122616.units AS units,
      o122616.iscancelled AS iscancelled,
      o122616.isfrombroker AS isfrombroker,
      o122616.isfromeditor AS isfromeditor,
      o130889.windowid AS windowid,
      o130889.windowname AS windowname,
      o122616.isclear AS isclear,
      o196398.hubname as Hubname,
      o122616.sellercompanyname
      || (CASE WHEN o122616.ordertype = 'Offer' THEN '*' ELSE NULL END)
      AS Seller,
      o122616.buyercompanyname
      || (CASE WHEN o122616.ordertype = 'Bid' THEN '*' ELSE NULL END)
      AS Buyer

      FROM ewindow.d_product o122610,
      ewindow.d_hub o196398,
      ewindow.d_strip o122611,
      ewindow.d_window_state o122612,
      ewindow.t_window o130889,
      ewindow.f_transaction o122616

      WHERE (o122610.productid = o122616.productid)
      AND (o130889.windowid = o122612.windowid)
      AND (o122611.stripid = o122616.stripid)
      AND (o196398.hubid = o122616.hubid)
      AND (o122612.window_state_id = o122616.window_state_id)
      AND (o122616.reforderid IS NOT NULL)
      AND (o122616.status = 'consummated')
      AND (o122616.trade_date >='01-Mar-2016')
      ORDER BY o122616.trade_date ASC , o122610.productname ASC;

      Concatenate
      LOAD buyerbrokercompanyname,
      Buyeruderid,
      instrument,
      Orderid,
      Price,
      Product,
      quantity,
      sellerbrokercompanyname,
      Selleruserid,
      Status,
      Strip_Name,
      Trade_Date,
      units,
      iscancelled,
      isfromeditor,
      isfrombroker,
      windowid,
      windowname,
      isclear,
      Hubname,
      sellercompanyname,
      Seller,
      Buyer
      from 'C:\Qlikview\Incremental_Load1.qvd';

      STORE TradeTBL into 'C:\Qlikview\Incremental_Load1.qvd';

      I got error "Field not found -<buyerbrokercompanyname> when Reload. Can someone advice please? I am trying to use Incremental Reload to speed up extraction record. Thanks!

        • Re: Incremental Reload Issue
          Marcus Sommer

          I think you are missing the file-format by storing and loading the qvd - I mean:

           

          'C:\Qlikview\Incremental_Load1.qvd' (qvd);

           

          - Marcus

            • Re: Incremental Reload Issue
              Jeremiah Lee

              Okay, could you suggest please what should I do to correct this?

                • Re: Incremental Reload Issue
                  Marcus Sommer

                  Add the bolded part - (qvd) - of my above suggestion to your store- and from-statements.

                   

                  - Marcus

                    • Re: Incremental Reload Issue
                      Jeremiah Lee

                      Added (qvd) to Store and From statement, same error.

                       

                      Any other suggestions?

                        • Re: Incremental Reload Issue
                          Marcus Sommer

                          Try it with changing Concatenate into Concatenate(TradeTBL) and also dropping the table after storing them:

                           

                          STORE TradeTBL into 'C:\Qlikview\Incremental_Load1.qvd' (qvd);

                          drop tables TradeTBL,

                           

                          - Marcus

                            • Re: Incremental Reload Issue
                              Jeremiah Lee

                              Hi Marcus,

                               


                              I tried but get error. It would be great if I can call you to discuss? Will be faster..

                                • Re: Incremental Reload Issue
                                  Marcus Sommer

                                  How looked the error-message?

                                   

                                  ps: one I see here: drop tables TradeTBL, because it's closed with a comma instead of an semi-colon: drop tables TradeTBL;

                                   

                                  - Marcus

                                    • Re: Incremental Reload Issue
                                      Jeremiah Lee


                                      This is the error I got..

                                       


                                      Field not found - <buyerbrokercompanyname>

                                      SQL SELECT

                                             o122616.buyerbrokercompanyname AS buyerbrokercompanyname,

                                             o122616.buyeruserid AS Buyeruserid,

                                             o122612.instrument AS instrument,

                                             o122616.orderid AS Orderid,

                                             o122616.price AS Price,

                                             o122610.productname as Product,

                                             o122616.quantity AS quantity,

                                             o122616.sellerbrokercompanyname AS sellerbrokercompanyname,

                                             o122616.selleruserid AS Selleruserid,

                                             o122616.status AS Status,

                                             o122611.stripname AS Strip_Name,

                                             o122616.trade_date AS Trade_Date,

                                             o122616.units AS units,

                                             o122616.iscancelled AS iscancelled,

                                             o122616.isfrombroker AS isfrombroker,

                                             o122616.isfromeditor AS isfromeditor,

                                             o130889.windowid AS windowid,

                                             o130889.windowname AS windowname,

                                             o122616.isclear AS isclear,

                                             o196398.hubname as Hubname,

                                             o122616.sellercompanyname

                                             || (CASE WHEN o122616.ordertype = 'Offer' THEN '*' ELSE NULL END)

                                                AS Seller,

                                             o122616.buyercompanyname

                                             || (CASE WHEN o122616.ordertype = 'Bid' THEN '*' ELSE NULL END)

                                                AS Buyer

                                           

                                        FROM ewindow.d_product o122610,

                                             ewindow.d_hub o196398,

                                             ewindow.d_strip o122611,

                                             ewindow.d_window_state o122612,

                                             ewindow.t_window o130889,

                                             ewindow.f_transaction o122616

                                                

                                        WHERE    (o122610.productid = o122616.productid)

                                             AND (o130889.windowid = o122612.windowid)

                                             AND (o122611.stripid = o122616.stripid)

                                             AND (o196398.hubid = o122616.hubid)

                                             AND (o122612.window_state_id = o122616.window_state_id)

                                             AND (o122616.reforderid IS NOT NULL)

                                             AND (o122616.status = 'consummated')

                                             AND (o122616.trade_date >='01-Jan-2016')

                                             AND (o122616.trade_date <='30-Apr-2016')

                                      ORDER BY o122616.trade_date ASC , o122610.productname ASC

                                        • Re: Incremental Reload Issue
                                          Marcus Sommer

                                          Ok. I think the reason is your renaming of the fields within the sql-satement by using a preceeding load above them. In your example you didn't any transformation within the preceeding load and you could leave them. Another approach would be to transfer all renamings from the sql into the preceeding load - which I would prefer if I had the need of at least one transformation - I mean, are no transformations necessary I wouldn't use a preceeding load and is there any transformation I would do all of them within the preceeding load.

                                           

                                          Beside transformations of any kind a renaming of fields could also be done with a renaming-statement maybe:

                                           

                                          for i = 1 to nooffields('TradeTBL')

                                               if left(fieldname($(i), 'TradeTBL'), 8) = 'o??????.' then

                                                    let vOldName = fieldname($(i), 'TradeTBL');

                                                    let vNewName = capitalize(subfield(fieldname($(i), 'TradeTBL'), '.', 2));

                                                    rename fields $(vOldName) into $(vNewName);

                                               end if

                                          next

                                           

                                          Also possible are renamings per mapping like: Rename fields using MapTab; which after all might be a lot more handier than to rename fields in each single load.

                                           

                                          - Marcus

                                            • Re: Incremental Reload Issue
                                              Jeremiah Lee

                                              Okay, honestly I am only trying to append new records from DB into exsiting records. Could you please provide the suggested script to do this please?

                                                • Re: Incremental Reload Issue
                                                  Marcus Sommer

                                                  Try:

                                                   

                                                  TradeTBL:
                                                  SQL SELECT
                                                  o122616.buyerbrokercompanyname AS buyerbrokercompanyname,
                                                  o122616.buyeruserid AS Buyeruserid,
                                                  o122612.instrument AS instrument,
                                                  o122616.orderid AS Orderid,
                                                  o122616.price AS Price,
                                                  o122610.productname as Product,
                                                  o122616.quantity AS quantity,
                                                  o122616.sellerbrokercompanyname AS sellerbrokercompanyname,
                                                  o122616.selleruserid AS Selleruserid,
                                                  o122616.status AS Status,
                                                  o122611.stripname AS Strip_Name,
                                                  o122616.trade_date AS Trade_Date,
                                                  o122616.units AS units,
                                                  o122616.iscancelled AS iscancelled,
                                                  o122616.isfrombroker AS isfrombroker,
                                                  o122616.isfromeditor AS isfromeditor,
                                                  o130889.windowid AS windowid,
                                                  o130889.windowname AS windowname,
                                                  o122616.isclear AS isclear,
                                                  o196398.hubname as Hubname,
                                                  o122616.sellercompanyname
                                                  || (CASE WHEN o122616.ordertype = 'Offer' THEN '*' ELSE NULL END)
                                                  AS Seller,
                                                  o122616.buyercompanyname
                                                  || (CASE WHEN o122616.ordertype = 'Bid' THEN '*' ELSE NULL END)
                                                  AS Buyer

                                                  FROM ewindow.d_product o122610,
                                                  ewindow.d_hub o196398,
                                                  ewindow.d_strip o122611,
                                                  ewindow.d_window_state o122612,
                                                  ewindow.t_window o130889,
                                                  ewindow.f_transaction o122616

                                                  WHERE (o122610.productid = o122616.productid)
                                                  AND (o130889.windowid = o122612.windowid)
                                                  AND (o122611.stripid = o122616.stripid)
                                                  AND (o196398.hubid = o122616.hubid)
                                                  AND (o122612.window_state_id = o122616.window_state_id)
                                                  AND (o122616.reforderid IS NOT NULL)
                                                  AND (o122616.status = 'consummated')
                                                  AND (o122616.trade_date >='01-Jan-2010')
                                                  AND (o122616.trade_date <='30-Feb-2016')
                                                  ORDER BY o122616.trade_date ASC , o122610.productname ASC;

                                                  STORE TradeTBL into 'C:\Qlikview\Incremental_Load1.qvd';
                                                  drop tables TradeTBL;

                                                  TradeTBL:
                                                  SQL SELECT
                                                  o122616.buyerbrokercompanyname AS buyerbrokercompanyname,
                                                  o122616.buyeruserid AS Buyeruserid,
                                                  o122612.instrument AS instrument,
                                                  o122616.orderid AS Orderid,
                                                  o122616.price AS Price,
                                                  o122610.productname as Product,
                                                  o122616.quantity AS quantity,
                                                  o122616.sellerbrokercompanyname AS sellerbrokercompanyname,
                                                  o122616.selleruserid AS Selleruserid,
                                                  o122616.status AS Status,
                                                  o122611.stripname AS Strip_Name,
                                                  o122616.trade_date AS Trade_Date,
                                                  o122616.units AS units,
                                                  o122616.iscancelled AS iscancelled,
                                                  o122616.isfrombroker AS isfrombroker,
                                                  o122616.isfromeditor AS isfromeditor,
                                                  o130889.windowid AS windowid,
                                                  o130889.windowname AS windowname,
                                                  o122616.isclear AS isclear,
                                                  o196398.hubname as Hubname,
                                                  o122616.sellercompanyname
                                                  || (CASE WHEN o122616.ordertype = 'Offer' THEN '*' ELSE NULL END)
                                                  AS Seller,
                                                  o122616.buyercompanyname
                                                  || (CASE WHEN o122616.ordertype = 'Bid' THEN '*' ELSE NULL END)
                                                  AS Buyer

                                                  FROM ewindow.d_product o122610,
                                                  ewindow.d_hub o196398,
                                                  ewindow.d_strip o122611,
                                                  ewindow.d_window_state o122612,
                                                  ewindow.t_window o130889,
                                                  ewindow.f_transaction o122616

                                                  WHERE (o122610.productid = o122616.productid)
                                                  AND (o130889.windowid = o122612.windowid)
                                                  AND (o122611.stripid = o122616.stripid)
                                                  AND (o196398.hubid = o122616.hubid)
                                                  AND (o122612.window_state_id = o122616.window_state_id)
                                                  AND (o122616.reforderid IS NOT NULL)
                                                  AND (o122616.status = 'consummated')
                                                  AND (o122616.trade_date >='01-Mar-2016')
                                                  ORDER BY o122616.trade_date ASC , o122610.productname ASC;

                                                  Concatenate (TradeTBL)
                                                  LOAD * from 'C:\Qlikview\Incremental_Load1.qvd';

                                                  STORE TradeTBL into 'C:\Qlikview\Incremental_Load1.qvd';

                                                   

                                                  The renaming of the fields would I do separately like above mentioned.

                                                   

                                                  - Marcus

                              • Re: Incremental Reload Issue
                                Jose Miguel Vilaplana Pascual

                                Where don't exist this field? In the qvd file or in the database?

                                 

                                Can you open the qvd file in a different document and see all the fields?