Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello guys,
I have a versioned list of documents stored locally like this:
Documents_Local:
Load * Inline
[
ID, Version, LastVersion, RecordDate, LastUpdated
1, 1, 'N', '01/01/2019', '01/01/2020'
1, 2, 'N', '01/01/2019', '01/01/2021'
1, 3, 'N', '01/01/2019', '01/01/2022'
1, 4, 'Y', '01/01/2019', '01/01/2023'
2, 1, 'Y', '01/01/2023', '01/01/2023'
];
As you can see, it has a RecordDate that never changes and a LastUpdated date that shows the last time that this row was updated. Existing rows are updated when a new version of the document is published because they have to change the value of LastVersion from 'Y' to 'N'.
From time to time I have to go check if any changes have been made to the table on the DB and update my Documents_Local table but since it is really big I only want to get updated rows (the rows in bold in the table below). Imagine that the table on the DB looks like this now:
Documents_DB:
Load * Inline
[
ID, Version, LastVersion, RecordDate, LastUpdated
1, 1, 'N', '01/01/2019', '01/01/2020'
1, 2, 'N', '01/01/2019', '01/01/2021'
1, 3, 'N', '01/01/2019', '01/01/2022'
1, 4, 'N', '01/01/2019', '01/01/2024'
2, 1, 'Y', '01/01/2023', '01/01/2023'
3, 1, 'Y', '01/01/2024', '01/01/2024'
1, 5, 'Y', '01/01/2019', '01/01/2024'
];
What I'm planning to do is to find the Max(LastUpdated) date to filter the rows:
Temp:
Load Date(Max(LastUpdated)) as MaxDate
Resident Documents_Local;
LET vMaxDate = Peek('MaxDate',0);
Drop Table Temp;
But at this point I get confused and I'm not sure what script I'm supposed to write to update the existing rows (rows where LastVersion and LastUpdated changed) and adding the new rows at the same time.
Does anyone see it more clearly?
Thanks!
Hi,
I believe this article might be helpful for you. Check "Insert and update (no delete)" part:
https://help.qlik.com/en-US/sense/February2024/Subsystems/Hub/Content/Sense_Hub/LoadData/use-QVD-fil...
Seems you need to get the Last Excecution Time of your query from SQL DB
In addition you will need an Primary Key which in your case should be concatenation of ID and Version. This will allow to uniquely identify records.
Let me know if sth is unclear for you
Hi,
I believe this article might be helpful for you. Check "Insert and update (no delete)" part:
https://help.qlik.com/en-US/sense/February2024/Subsystems/Hub/Content/Sense_Hub/LoadData/use-QVD-fil...
Seems you need to get the Last Excecution Time of your query from SQL DB
In addition you will need an Primary Key which in your case should be concatenation of ID and Version. This will allow to uniquely identify records.
Let me know if sth is unclear for you
Thanks Arek! That's the info I was looking for. I just had to create a synthetic key with autonumber().
After modifying my previous code this seems to work just fine, I don't know if I can optimise it though: