Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
rakeshpaul
New 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
MVP
MVP

Re: Incremental Load

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
New Contributor

Re: Incremental Load

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

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
Community Browser