issues with incremental load

vireshkolagimat — Wed, 06 Sep 2017 08:36:45 GMT

Hi All,

I have QVD file which was generated by SQL server database.

Now i am generating the another QVD file from oracle database (the table in oracle has the same structure like the table in SQL server) and trying to concatenate with the existing QVD file which was generated by the SQL server database.

But after incremental load, its over writing the existing QVD file and keeping only the data which is from oracle database.

Kindly suggest me how to to fix it.

Regards,

Viresh

Re: issues with incremental load

sunny_talwar — Wed, 06 Sep 2017 08:43:02 GMT

What is the exact script you are using?

Re: issues with incremental load

vireshkolagimat — Wed, 06 Sep 2017 08:57:42 GMT

Hi, here is the code:

/* Setup for incremental load. */

SET vQvdFile='C:\Users\VireshK\Desktop\Migration\AB\Data\QVD\Raw_Qvds\ConsSalesHistory.qvd'; //?? The QVD filename

SET vTableName='SalesHistory'; //?? The name of the QV table we are loading on the Data Load tab

SET vPK='SalesHistoryId'; //?? Data Primary Key

// Set a variable indicating if the QVD exists or not. -1 is True, 0 is False

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

IF $(vQvdExists) THEN // QVD exists, we will do an incremental reload

maxdateTab:

// Get the max date from this QVD for use in incremental SELECT

LOAD max(InvoiceDate) as maxdate //?? Set the name of the date or datetime field

FROM $(vQvdFile) (qvd);

LET vMaxdate = Date(Peek('maxdate',0,'maxdateTab'),'YYYY-MM-DD');

LET vLast30date = Date(Peek('maxdate',0,'maxdateTab')-30,'DD-MMM-YY');

IF IsNull(vMaxdate) then

LET vIncrementalExpression = '';

ELSE

// LET vIncrementalExpression = 'Where YEAR (salhist.dated) >= Year (GETDATE ()) - 3 and Convert(Varchar(8),salhist.dated, 112) >=' & Chr(39)& $(vLast30date) &Chr(39) &

LET vIncrementalExpression = 'and CUSTOMERCODE not in (''L'',''M'',''N'') and InvoiceDate >=' & Chr(39)& '$(vLast30date)' &Chr(39) &

' and InvoiceDate <=' &Chr(39)& date(vReloadTime,'DD-MMM-YY') &Chr(39);

ENDIF;

DROP table maxdateTab;

ELSE // QVD does not exist

//LET vIncrementalExpression = 'Where CUSTOMERCODE not in (''CCRE001'',''CCRE002'',''CNRE001'') and YEAR(INVOICEDATE) >= (Year (sysdate()) - 3)';

LET vIncrementalExpression = 'and CUSTOMERCODE not in (''L'',''M'',''N'') and extract( YEAR from InvoiceDate) >= (extract (YEAR from sysdate)-3)'; // No QVD. Force full reload

END IF

/* End of Setup for incremental load. */

Load the incremental changes or full reload.

This is a standard LOAD (or SQL SELECT) statement.

$(vTableName):

LOAD *;

SQL SELECT DIVISION_CODE,

DIVISION_NAME,

CUSTOMERCODE,

WAREHOUSECODE,

PRODUCTCODE,

INVOICENO,

INVOICEDATE,

SALESCOST,

SALESLISTVALUE,

SALESCOMMISSION,

SALESCUSTOMERGROUP,

SALESTERRITORY,

REGIONCODE,

CHANNELCODE,

SALESMANCODE,

ROUTECODE,

SALESUNIT,

SALESUNITFACTOR,

RETURNTYPE,

SALESQTY,

SALESVALUE

FROM APPS.XXEGC_QV_SALES_HISTORY_V

where DIVISION_CODE='A'

// If doing a SQL SELECT, the vIncrementalExpression would be part of the SQL SELECT statement.

$(vIncrementalExpression); // Include WHERE clause created in "Incremental Setup" tab

/*End of Load*/

Update the QVD with changes.

Directory C:\Users\VireshK\Desktop\Migration\AB\Data\QVD\Raw_Qvds;

// If incremental reload was , load previous data and concatenate to data just read.

IF $(vQvdExists) THEN

// Use CONCATENATE in case we've added any new fields.

CONCATENATE ($(vTableName))

LOAD Distinct * FROM $(vQvdFile) (qvd)

WHERE InvoiceDate < '$(vLast30date)'; // Load only QVD rows that were not already loaded in the data load.

END IF;

Overwrite the QVD with the QV datatable.

If ScriptErrorCount = 0 then

Call StoreAndDrop(vTableName,vQvdFile);

ENDIF;

SET vQvdFile='';

SET vTableName='';

SET vPK='';

exit Script

thank you.

Re: issues with incremental load

vireshkolagimat — Thu, 07 Sep 2017 15:06:07 GMT

Hi Sunny,

Could you please let me know if you are able to figure out the issue.

thank you.

Regards,

Viresh

topic Re: issues with incremental load in QlikView

issues with incremental load

Re: issues with incremental load

Re: issues with incremental load

Re: issues with incremental load