Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
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
Highlighted
Partner
Partner

Re: Issue with Incremental Loading in the load script

use 'SET' instead of 'LET'

Regards

Highlighted
Not applicable

Re: Issue with Incremental Loading in the load script

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.

Highlighted
MVP & Luminary
MVP & Luminary

Re: Issue with Incremental Loading in the load script

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
Highlighted
Not applicable

Re: Issue with Incremental Loading in the load script

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.

Highlighted
MVP & Luminary
MVP & Luminary

Re: Issue with Incremental Loading in the load script

That means the the variable last_reload is empty.


talk is cheap, supply exceeds demand
Highlighted
Not applicable

Re: Issue with Incremental Loading in the load script

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.

Highlighted
MVP & Luminary
MVP & Luminary

Re: Issue with Incremental Loading in the load script

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
Highlighted
Not applicable

Re: Issue with Incremental Loading in the load script

Thanks.  Got it now.