topic Re: Incremental Load in QlikView

Incremental Load

rakeshpaul — Thu, 17 Mar 2016 08:42:26 GMT

Dear All,

I am trying to implement incremental load on a transaction table where I am picking up data from ORACLE R12. I am using these part of code, and I have another piece of code where basically I am concatenating with the history table, here is the code-

Let V_StartTime=Now();

Let V_CYearStart=Date(YearStart(Today()),'DD-MMM-YYYY'); //Current Year Start Date

NoConcatenate

PA_EXPENDITURE_ITEMS_ALL_HIST:

LOAD *

From $(vMDRawQVDs)PA_EXPENDITURE_ITEMS_ALL.qvd (qvd);

NoConcatenate

LY_MaxDate:

Load max(Date(Date#(LAST_UPDATE_DATE,'DD-MMM-YYYY'),'DD-MMM-YYYY')) AS LYearEnd Resident PA_EXPENDITURE_ITEMS_ALL_HIST;

Let V_LYearEnd=Peek('LYearEnd',0,'LY_MaxDate'); //Last Year Start Date

Drop Table LY_MaxDate;

NoConcatenate

PA_EXPENDITURE_ITEMS_ALL:

Sql

Select

EXPENDITURE_ITEM_ID,

To_Char(LAST_UPDATE_DATE,'DD-MON-YYYY') LAST_UPDATE_DATE,

ORG_ID

FROM

PA_EXPENDITURE_ITEMS_ALL

Where

To_Char(LAST_UPDATE_DATE,'DD-MON-YYYY')>'$(V_CYearStart)';

But the problem is that while I am executing these piece of code instead of taking updated data it's taking the full table. Here is a small piece of data which is residing in ORACLE system-

EXPENDITURE_ITEM_ID	LAST_UPDATE_DATE	ORG_ID
1307	9/26/2011 9:14:45 PM	98
1308	9/29/2011 9:31:11 AM	98
1309	9/26/2011 9:14:45 PM	98
1310	9/26/2011 9:14:45 PM	98
1311	9/26/2011 9:14:45 PM	98

Please suggest me how can I tackle this issue.

Thanks in advance!

Regards,

Rakesh Paul

Re: Incremental Load

jonathandienst — Thu, 17 Mar 2016 15:27:59 GMT

You are doing a text comparison in the SQL expression where clause, and the date format is not suitable for such an inequality.

Change the date threshold to

Let V_CYearStart=Date(YearStart(Today()), 'YYYY-MM-DD');

And the SQL where to

Where

To_Char(LAST_UPDATE_DATE,'YYYY-MM-DD') > '$(V_CYearStart)';

Or do the date calculation using Oracle SQlL logic and compare dates with dates.

Re: Incremental Load

rakeshpaul — Thu, 17 Mar 2016 15:34:23 GMT

Hi Jonathan,

Thank you for your response. I will definitely try your solution, by the time I used another approach and it worked for me. I used this piece of code-

Try using this ‘trunc(LAST_UPDATE_DATE)<=trunc(to_date('31-DEC-2015'))' in the query in place of ‘to_char(LAST_UPDATE_DATE,'MM/DD/YYYY')<='12/31/2015’.

I tried hard coded the numbers and it worked and by the time I will make it dynamic instead of hardcoading the numbers.

Thanks again!

Regards,

Rakesh Paul

Re: Incremental Load

Gysbert_Wassenaar — Thu, 17 Mar 2016 15:35:40 GMT

Tip: use Qlikview Components to do your incremental reloading: http://qlikviewnotes.blogspot.nl/2012/01/incremental-load-using-qlikview.html