Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Issue with Incremental Loading in the load script

Hi,

I am creating a variable as shown -

LET INCREMENTAL_EXP='where trunc(pxcreatedatetime)>=to_date('&Chr(39)&FieldValue($(last_reload),1)&Chr(39)&',''MM/DD/YYYY'')
or trunc(pxupdatedatetime)>=to_date('&
Chr(39)&FieldValue($(last_reload),1)&Chr(39)&',''MM/DD/YYYY'')'; 

In the debugger, I see, it is the date value and it doesn't show any error.  Then, I am trying to use this variable in my Oracle SQL query as shown below -

select * from guidelines.beacon_mg_attest where case_id in (select pyid from beacon.cases $(INCREMENTAL_EXP));

Here, when $(INCREMENTAL_EXP) gets resolved, the date portion is blank.  Please help me to understand what I am missing here.

Thanks,

Sudha.

8 Replies
fredericmante
Partner - Contributor III
Partner - Contributor III

use 'SET' instead of 'LET'

Regards

Not applicable
Author

I had tried that and it is not working.  If I use SET, the variable within the variable is not getting evaluated at run time.

Thanks,

Sudha.

Gysbert_Wassenaar

Try using single quotes around the dollar expansion of the variable:

select * from guidelines.beacon_mg_attest where case_id in (select pyid from beacon.cases '$(INCREMENTAL_EXP)');


And make sure the variable last_reload contains a string value in the date format MM/DD/YYYY


talk is cheap, supply exceeds demand
Not applicable
Author

Tried that...still not working.

This is what I see at run time, when I used the quotes you had suggested -

select * from guidelines.beacon_mg_attest where case_id in (select pyid from beacon.cases 'where trunc(pxcreatedatetime)>=to_date('''',''MM/DD/YYYY'')

                            or trunc(pxupdatedatetime)>=to_date('''',''MM/DD/YYYY'')')

Thanks,

Sudha.

Gysbert_Wassenaar

That means the the variable last_reload is empty.


talk is cheap, supply exceeds demand
Not applicable
Author

When I run in debug mode, I can see it is getting evaluated correctly in the variable INCREMENTAL_EXP but when trying to use the variable in the query, it does not.  Please have a look at the attachment.

Thanks,

Sudha.

Gysbert_Wassenaar

The fieldvalue function only works on fields, not variables. So fieldvalue($(last_variable), 1) will return nothing since there isn't a field with the name 42459.614351852 or 3/30/2016 2:44:40 PM.


talk is cheap, supply exceeds demand
Not applicable
Author

Thanks.  Got it now.