Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
Please try below:
where ticket_start > '$(vLastLoadStart)' or ticket_end > '$(vLastLoadStart)';
Sorry I missed out $ in where ticket_start > '$(vLastLoadStart)' or ticket_end > '$(vLastLoadStart)';
but this does not work also.
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.
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.