Qlikview incremental load with 2 different timestamps

[loke2014]

Hi,

Can someone tell me what is wrong with my code below?

I have tried to implement incremental load (insert &update) for helpdesk ticketing reporting using Qlikview without any success as some older rows are missing. Example:

new_table:

LOAD

ticket_id,

ticket_subject,

ticket_start,

ticket_end

FROM .....where ticket_start > $(vLastLoadStart) or ticket_end>$(vLastLoadStart); //if I remove WHERE statement here then the data is correct, but I have to load everything from SQL here

Concatenate

LOAD

ticket_id,

ticket_subject,

ticket_start,

ticket_end

FROM .....(QVD) where not exists (ticket_ID);  //ticket.qvd

store into .......(QVD); //ticket.qvd

drop table new_table;

ticket_qvd:

LOAD *

from .....(QVD);  //ticket.qvd

Last_updated_date_table:
load
MAX(ticket_start_date) as max_ticket_start_date
resident ticket_qvd
;

LET vLastLoadStart = (peek('max_ticket_start_date', 0,'Last_updated_date_table'));

NOTE: ticket_end can be blank if the ticket is not solve as it is still pending with no end date. ticket_start and ticket_end are timestamp eg.

2018-02-01 14:10:58

[rahulpawarb]

Please try below:

where ticket_start > '$(vLastLoadStart)' or ticket_end > '$(vLastLoadStart)';

[loke2014]

Sorry I missed out $ in where ticket_start > '$(vLastLoadStart)' or ticket_end > '$(vLastLoadStart)';

but this does not work also.

[jonathandienst]

If I understand you correctly, you are performing a last load time based incremental load and there is some older data missing? it sounds to me like you will have to perform a full reload to get the corrected history and then store that in your QVD. You can then continue to use your incremental load.

[loke2014]

Hi Jonathan,

Yes, I did a full reload without the where ticket_start > '$(vLastLoadStart)' or ticket_end > '$(vLastLoadStart)';

Example: 1000 records.

After that, I insert back where ticket_start > '$(vLastLoadStart)' or ticket_end > '$(vLastLoadStart)'; and rerun the load script again. The older data disappear. Try again a few times, still the same result.

Now it show less than 50 records only, but it does contain the latest timestamp entry.