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

[#2]

Your variable $(vLastModifiedDate) is empty

[Anonymous]

could you please suggest how to fix it.

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

[#4]

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');

[rajuamet]

Hi Javed Khan,

Try this.

sort_resolved:

Load

Max([Full Last modified date]) as MaxDate

Resident Resolved;

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

[settu_periasamy]

Hi Javed,

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

Or

Just for check, Manually enter the date

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

[Anonymous]

thanks for your quick response.

now worked same error, empty value passed.

[Anonymous]

vLastModifiedDate is not assigned while debugging.

[rajuamet]

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.

[Anonymous]

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);