QlikView App Dev

[#1]

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

[#2]

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;