topic Re: Error in Incremental Load in QlikView

Error in Incremental Load

Anonymous — Mon, 16 Nov 2015 14:47:32 GMT

Hi Colleagues,

I am new to QlikView.

below is the script i am trying to implement Incremental Load.

After Initial Load and save it in QVD file Resolved.qvd

i have written below script to get the max last modified date

===============================================================================

Resolved:

LOAD [Ticket Number],

Priority,

[Event Type],

[Process Type],

Company,

Tower,

Status,

[Full Resolved Date],

[Full Last modified date],

[Ticket Age],

Organisation,

Resolved_date,

Month_Resolved

FROM

$(vSavedQVD)Resolved.qvd

(qvd);

sort_resolved:

Load *

Resident Resolved

order by [Full Last modified date];

LET vLastModifiedDate = Peek('[Full Last modified date]',-1,'sort_resolved');

DROP Table Resolved;

============================================================================

Now applying incremental load script below

Incremental:

NoConcatenate

LOAD "Ticket Number",

Priority,

"Event Type",

"Process Type",

Company,

Tower,

Status,

"Full Resolved Date",

"Full Last modified date",

"Ticket Age",

Organisation,

"Resolved_date",

"Month_Resolved";

SQL SELECT "Ticket Number",

Priority,

"Event Type",

"Process Type",

Company,

Tower,

Status,

"Full Resolved Date",

"Full Last modified date",

"Ticket Age",

Organisation,

"Resolved_date",

"Month_Resolved"

FROM "Analytics_SDM12".dbo."Resolved_Tickets"

WHERE (Resolved_date > GETDATE() - 30) AND "Full Last modified date" >= $(vLastModifiedDate);

==============================================================================

and the final load

Concatenate

LOAD [Ticket Number],

Priority,

[Event Type],

[Process Type],

Company,

Tower,

Status,

[Full Resolved Date],

[Full Last modified date],

[Ticket Age],

Organisation,

Resolved_date,

Month_Resolved

FROM

$(vSavedQVD)Resolved.qvd

(qvd)

where not exists([Ticket Number]);

store Incremental into $(vSavedQVD)Resolved.qvd(qvd);

DROP Table Incremental;

========================================================================

i have attached the error i am getting please help me out in this

Thanks,

Javed Khan

Re: Error in Incremental Load

Mon, 16 Nov 2015 15:41:13 GMT

Your variable $(vLastModifiedDate) is empty

Re: Error in Incremental Load

Anonymous — Mon, 16 Nov 2015 16:30:59 GMT

could you please suggest how to fix it.

as i am using Peek function to get it, but value not coming in that varaiable

Re: Error in Incremental Load

Mon, 16 Nov 2015 16:34:05 GMT

your sort_resolved auto concatenated to Resolved

you ether do

sort_resolved:

noconcatenate

Load *

Resident Resolved

order by [Full Last modified date];

or don't create sort_resolved at all

do max( [Full Last modified date])

Resolved:

LOAD nax([Full Last modified date]) as [Full Last modified date]

FROM

$(vSavedQVD)Resolved.qvd

(qvd);

LET vLastModifiedDate = Peek('[Full Last modified date]',-1,'Resolved');

Re: Error in Incremental Load

rajuamet — Mon, 16 Nov 2015 16:37:35 GMT

Hi Javed Khan,

Try this.

sort_resolved:

Load

Max([Full Last modified date]) as MaxDate

Resident Resolved;

LET vLastModifiedDate = Peek('MaxDate',0,'sort_resolved');

Re: Error in Incremental Load

settu_periasamy — Mon, 16 Nov 2015 16:41:58 GMT

Hi Javed,

Can you Debug the Script, Like the vLastModifiedDate is assigned or not?

Just for check, Manually enter the date

WHERE (Resolved_date > GETDATE() - 30) AND "Full Last modified date" >= '01/01/2015';

Re: Error in Incremental Load

Anonymous — Mon, 16 Nov 2015 16:42:32 GMT

thanks for your quick response.

now worked same error, empty value passed.

Re: Error in Incremental Load

Anonymous — Mon, 16 Nov 2015 16:47:01 GMT

vLastModifiedDate is not assigned while debugging.

Re: Error in Incremental Load

rajuamet — Mon, 16 Nov 2015 16:59:09 GMT

Hi Javed,

I think you should check Resolved table first. See whether data has been fetched and check the field [Full Last modified date].

Make sure this field is not empty.

Re: Error in Incremental Load

Anonymous — Mon, 16 Nov 2015 17:07:18 GMT

i have created resolved.QVD file from sql server database.

when i load data from qvd only it is working fine data is coming in [Full Last modified date] field.

but the issues is that while implementing incremental load i m trying to get max date of [Full Last modified date] it not getting reflected in variable i do not know why this is happening.

below is the initial load script:-

SDM12_Resolved:

LOAD "Ticket Number",

Priority,

"Event Type",

"Process Type",

Company,

Tower,

Status,

"Full Resolved Date",

"Full Last modified date",

"Ticket Age",

Organisation,

"Resolved_date",

"Month_Resolved";

SQL SELECT "Ticket Number",

Priority,

"Event Type",

"Process Type",

Company,

Tower,

Status,

"Full Resolved Date",

"Full Last modified date",

"Ticket Age",

Organisation,

"Resolved_date",

"Month_Resolved"

FROM "Analytics_SDM12".dbo."Resolved_Tickets"

WHERE (Resolved_date > GETDATE() - 30);

Store SDM12_Resolved into $(vSavedQVD)Resolved.qvd(qvd);

Re: Error in Incremental Load

Anonymous — Mon, 16 Nov 2015 17:13:15 GMT

Hi yury.tom,

I tried both of your suggestion , but failed to success.

does it affect if we make qvd from MS SQL Server database and top of that try to implement incremental load , i do not think so.

Re: Error in Incremental Load

Peter_Cammaert — Mon, 16 Nov 2015 17:30:33 GMT

If you are using the Max() function, change the subsequent LET statement into:

LET vLastModifiedDate = Peek('[Full Last modified date]', 0, 'Resolved');

Make sure that both the fieldname and table name refer to existing objects. Peek() won't complain if they don't. You'll get a NULL() value instead.

Re: Error in Incremental Load

Peter_Cammaert — Mon, 16 Nov 2015 17:34:32 GMT

BTW is the field [Full Last modified date] being recognised by QlikView as a DATE or a TIMESTAMP field (or as a numerical equivalent)? If not (for example because the date string has the wrong order or contains invalid characters), Max() won't do what it is expected to do and return nothing.

Peter

Re: Error in Incremental Load

Anonymous — Mon, 16 Nov 2015 17:48:13 GMT

Hi Peter,

I have loaded data from SQL Server database, i m really do no kknow which formate qlikview recodnise it.

can we do some workaround do check date format.

Re: Error in Incremental Load

Mark_Little — Mon, 16 Nov 2015 17:58:31 GMT

Hi,

I would be working through on a step by step basis, you know they vairable is not being populated, so the next step is seeing if the below receives what you expect.

Resolved:

LOAD Max([Full Last modified date]) as [Full Last modified date]

FROM

$(vSavedQVD)Resolved.qvd

(qvd);

Adding Trace commands can help also.

I.e. Trace $(vLastModifiedDate);

after your peek.

Mark

Re: Error in Incremental Load

jonas_rezende — Mon, 16 Nov 2015 18:21:54 GMT

Hi, Javed Khan.

Verify if is the format of the field is correct, add the Date () function, and includes among the tables NoConcatenate command.

Resolved:

LOAD [Ticket Number],

Priority,

[Event Type],

[Process Type],

Company,

Tower,

Status,

[Full Resolved Date],

[Full Last modified date],

[Ticket Age],

Organisation,

Resolved_date,

Month_Resolved

FROM

$(vSavedQVD)Resolved.qvd

(qvd);

NoConcatenate

sort_resolved:

Load

Max(Date([Full Last modified date])) as MaxDate

Resident Resolved;

LET vLastModifiedDate = Peek('MaxDate',0,'sort_resolved');

Drop table Resolved;

Hope this helps!

Re: Error in Incremental Load

effinty2112 — Mon, 16 Nov 2015 18:43:11 GMT

Hi,

Take out the square brackets in this line:

LET vLastModifiedDate = Peek('[Full Last modified date]',-1,'sort_resolved');

replace with

LET vLastModifiedDate = Peek('Full Last modified date',-1,'sort_resolved');

It's easy to get this wrong. The script needs a string - the table's name not the table itself if you know what I mean.

regards

Andrew