4 Replies Latest reply: Nov 23, 2012 6:57 AM by Deepayan Chatterjee RSS

    Incremental Load without date?

      Hi I have primary keys in a table:

       

      LOAD

      `product_sk`,

         
      `merchant_sk`,

         
      `brand_name`,

         
      `product_id`,

         
      `product_name`,

         
      `redemption_type`,

         
      `product_type`,

         
      `category_id`,

         
      `category_name`,

         
      `release_date`,

         
      `currency`,

         
      `net_retail_price`,

         
      discount,

         
      `vat_cost`,

         
      `is_active`,

         
      `article_id`,

         
      `product_description`,

         
      ValueDocumentType;

      SQL SELECT *

       

      Here product_sk is primary key can any one please suggest a incremental load using primary key? Please note that in several huge tables we do not have date dimension. I am looking forward for a crashproof script that should not use temp date like Let today's date = UTC, because many days data job failed then I always required to load full table again.

       

      Can you please help me with some script where it will check max product_sk in QVD and tally it with My SQL, then load?

       

      Many Thanks

      - Deep

        • Re: Incremental Load without date?

          Maybe this might be of help

           

          QV_Table:
          SQL SELECT Primary Key,X,Y From DB_Table
          Where ModificationTime>=#$(LastExecTime)#;

           

          Concatenate LOAD Primary Key,X,Y From File.QVD

          WHERE NOT Exists (PrimaryKey);

           

          STORE QV_Table INTO File.QVD;

           

          Regards

          Mhatim

            • Re: Incremental Load without date?
              Jagan Nalla

              Hello,

               

              Here is the code:

              OldQVD:

              LOAD     

                   Product_sk

              FROM

              Main.qvd (qvd);

               

               

              MaxOldQVD:

              LOAD

              Max(Product_sk) as MaxProduct_sk

              Resident OldQVD;

               

               

              LET vMaxProduct_sk = Peek('MaxProduct_sk',-1,MaxOldQVD);

               

               

              DROP Table OldQVD;

              DROP Table MaxOldQVD;

               

               

              Main:

              LOAD *

              FROM

              Main.qvd;

                

              Concatenate

              Load * ;

              Select * from dbo.Main

              where Product_sk > $(vMaxProduct_sk);

               

               

              STORE Main into Main.qvd;

               

               

              Hope it helps you..

               

              Cheers!!

              Jagan

                • Re: Incremental Load without date?

                  It works perfect! Thank You for suggession, I made some changes so that even for 1st time if it do not find main qvd, it will generate. OR It will fetch data from main. Here it is:

                   

                   

                   

                   

                   

                  Let vQVDPath='C:\Users\...\Main.qvd';

                  if not IsNull(QvdCreateTime('$(vQVDPath)')) then

                  OldQVD:

                  LOAD    

                      
                  product_sk

                  FROM
                  [C:\Users\...\Main.qvd] (
                  qvd);



                  MaxOldQVD:

                  LOAD

                  Max(product_sk) as max_product_sk

                  Resident OldQVD;





                  LET vMaxProduct_sk = Peek('max_product_sk',-1,MaxOldQVD);





                  DROP Table OldQVD;

                  DROP Table MaxOldQVD;



                  Main:

                  LOAD *

                  FROM

                  [C:\Users\...\Main.qvd] (
                  qvd);

                   

                  Concatenate

                  Load * ;

                  Select * FROM `ll_etl`.`test_qv_load`

                  where product_sk >
                  $(vMaxProduct_sk);



                  STORE Main into [C:\Users\...\Main.qvd];



                  ENDIF

                  // full query

                  if IsNull(QvdCreateTime('$(vQVDPath)')) then

                  Main:

                  Select * FROM `ll_etl`.`test_qv_load`;

                  STORE Main into [C:\Users\...\Main.qvd];



                  ENDIF

                   

                   

                  Hope it will help others!!! Thank You!

                • Re: Incremental Load without date?

                  That Helps But I wanted with no time stamp.