1 Reply Latest reply: Feb 8, 2014 9:24 PM by Srikanth P RSS

    URGENT  after writing this script  what are the remaining steps in incremental process

    manoj kumar

      set vvpath=M:\ONLYQLIKVIEW\DOCUMENTS\MAPPING;-----VARIABLE DECLARATION

       

      sales:

      LOAD orderid,       ------------LOADING TABLE

           productid,

           customerid,

           gross_amount,

           orderDate

      FROM

      [..\MAPPING\2\map.xlsx]       

      (ooxml, embedded labels, table is sales);

       

      store sales into $(vvpath)\sales.qvd;-------------------GENERATED QVD

       

      sales:

       

      LOAD orderid,

           productid,

           customerid,

           gross_amount,

           orderDate

      FROM

      [..\MAPPING\2\map.xlsx]

      (ooxml, embedded labels, table is sales)

      where orderDate>=12/10/2012;---------------------------INSERTED DATA

       

       

      Concatenate

      LOAD orderid,

           productid,

           customerid,

           gross_amount,

           orderDate

      FROM

      [..\..\ONLYQLIKVIEW\DOCUMENTS\MAPPING\sales.qvd]

      (qvd)

      where not exists(orderid);---------------------------CONCATENATE QVD

       

       

      store sales into $(vvpath)\sales.qvd;---------------STORED INTO QVD

       

       

       

       

      AFTER THIS I WANT INSERT AND DELETE SOME RECORDS FOR THAT WHAT I HAVE TO DO.

       

      PLS CHECK THAT ABOVE SCRIPT IS RIGHT OR NOT . I AM NEW TO QLIK VIEW

       

      STILL HOW MANY STEPS ARE THERE TO FINISH INCREMENTAL LOADING ,INSERT,(INSERT,UPDATE),(INSERT,UPDATE,DELETE)

       

       

      THANKS IN ADVANCE

        • Re: URGENT  after writing this script  what are the remaining steps in incremental process
          Srikanth P

          Please find the below script.

           

          SET vQVDPath = 'M:\ONLYQLIKVIEW\DOCUMENTS\MAPPING' ; -- QVD Path

           

          // First check the Sales.qvd exist in the qvd folder. If QVD exists then do Incremental Load else Full Load

           

          LET vQVDExists = IF(FileSize('$(vQVDPath)\Sales.qvd' > 0 , -1 , 0) ; // This variable retrun -1 if QVD exist otherwise 0

           

          IF $(vQVDExists) THEN

          // QVD exist , do Incremental Load


          // First Load the Max Order date from the qvd & store into variable

          MaxOrderDate:

          LOAD Max(orderdate) AS MAXDATE FROM [$(vQVDPath)\sales.qvd] (qvd) ;

           

          LET vMaxOrderDate = Peek('MAXDATE') ;

           

          DROP Table MaxOrderDate;

           

          sales:

          LOAD orderid,     // NEW Data Loading (INSERT ,UPDATE)

               productid,

               customerid,

               gross_amount,

               orderDate

          FROM [..\MAPPING\2\map.xlsx] (ooxml, embedded labels, table is sales)

          Where orderdate>$(vMaxOrderDate);

           

          CONCATENATE (SALES) // In this Load old data & delete the updated old data

          LOAD * FROM [$(vQVDPath)\sales.qvd] (qvd)

          WHERE Not Exists(orderid);

           

          Inner Join (Sales) // In this delete the deleted data from table with Inner join on orderid

          LOAD orderid FROM [..\MAPPING\2\map.xlsx] (ooxml, embedded labels, table is sales) ;

           

          ELSE

          // QVD doesn't exist , do Full Load first time

          sales:

          LOAD orderid,    

               productid,

               customerid,

               gross_amount,

               orderDate

          FROM [..\MAPPING\2\map.xlsx] (ooxml, embedded labels, table is sales);

           

          ENDIF

          //Finally the store the table into QVD

          store sales into [$(vQVDPath)\sales.qvd] (qvd) ;

           

          DROP Table Sales;