Error in Incremental Load

[Anonymous]

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

[Anonymous]

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.

[Peter_Cammaert]

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.

[Peter_Cammaert]

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

[Anonymous]

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.

[Mark_Little]

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

[jonas_rezende]

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!

[effinty2112]

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