topic Re: Issue with Incremental Loading in the load script in QlikView

Issue with Incremental Loading in the load script

Mon, 04 Apr 2016 15:59:01 GMT

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.

Re: Issue with Incremental Loading in the load script

fredericmante — Mon, 04 Apr 2016 16:02:21 GMT

use 'SET' instead of 'LET'

Regards

Re: Issue with Incremental Loading in the load script

Mon, 04 Apr 2016 17:17:24 GMT

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.

Re: Issue with Incremental Loading in the load script

Gysbert_Wassenaar — Mon, 04 Apr 2016 17:20:09 GMT

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

Re: Issue with Incremental Loading in the load script

Mon, 04 Apr 2016 17:24:21 GMT

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.

Re: Issue with Incremental Loading in the load script

Gysbert_Wassenaar — Mon, 04 Apr 2016 17:26:03 GMT

That means the the variable last_reload is empty.

Re: Issue with Incremental Loading in the load script

Mon, 04 Apr 2016 17:29:03 GMT

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.

Re: Issue with Incremental Loading in the load script

Gysbert_Wassenaar — Mon, 04 Apr 2016 17:36:24 GMT

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.

Re: Issue with Incremental Loading in the load script

Mon, 04 Apr 2016 18:43:53 GMT

Thanks. Got it now.