Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

QVD Incremental load from Stored Procedure using Cookbook

I am new to clickview but have learned about Rob's Cookbook.  I have created some QVD's that load incrementally that pull directly from a table but can someone help me in getting that to work with a stored procedure where parameters are being passed. 

//Setup 

SET vQvdFile='..\ExtractQVD\QVDNAME.qvd';

SET vTableName='TABLENAME';

SET vPK='PRIMARYKEY';

LET vQvdExists = if(FileSize('$(vQvdFile)') > 0, -1, 0);

IF $(vQvdExists) THEN

          maxdateTab:

          LOAD max(OrderDate) as maxdate

          FROM $(vQvdFile) (qvd);

          LET vIncrementalExpression = 'WHERE OrderDate >=' & peek('maxdate');

          DROP table maxdateTab;

 

ELSE

          LET vIncrementalExpression = '';

END IF

//Load Script

$(vTableName):

Directory;

LOAD SystemOrderDetailID,          //Primary Key

     OrderDate,                              // Row Modification date

     OrderNumber,

     ReceiptNumber,

     CustomerID

FROM

'..\Desktop\gcjrptphonesales.csv'

(txt, codepage is 1252, embedded labels, delimiter is ',', msq)

$(vIncrementalExpression)

;

//Update QVD

Directory;

IF $(vQvdExists) THEN

          CONCATENATE ($(vTableName)) LOAD * FROM $(vQvdFile) (qvd)

          WHERE NOT exists($(vPK))

          ;

END IF

INNER JOIN ($(vTableName))

LOAD $(vPK)

FROM

'..\Desktop\gcjrptphonesales.csv '

(ansi, txt, delimiter is ',', embedded labels, msq)

;

STORE $(vTableName) INTO $(vQvdFile);

Call log('$(vQvdFile) updated, rows=' & QvdNoOfRecords('$(vQvdFile)'))

---------------------------

Now here is the Stored Procedure I would like to insert into the scrip above but not sure how as I have yet to get it to work.

Sales:

LOAD SystemOrderDetailID,          //Primary Key

     OrderDate,                              // Row Modification date

     OrderNumber,

     ReceiptNumber,

     CustomerID

 

SQL

          EXEC DB..sp_CustomerSales '$(DATEFROM)', '$(DATETO)';

Thanks for your help.

0 Replies