Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
rakeshpaul
Partner - Contributor
Partner - Contributor

Incremental Load

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_IDLAST_UPDATE_DATEORG_ID
13079/26/2011 9:14:45 PM98
13089/29/2011 9:31:11 AM98
13099/26/2011 9:14:45 PM98
13109/26/2011 9:14:45 PM98
13119/26/2011 9:14:45 PM98

Please suggest me how can I tackle this issue.

Thanks in advance!

Regards,

Rakesh Paul

3 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
rakeshpaul
Partner - Contributor
Partner - Contributor
Author

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

Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand