Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

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

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

1 Reply
Not applicable

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

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;