Qlik Data Load Editor: Script that updates row

[marceloromero]

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!

[Arek92 · Accepted solution]

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

[marceloromero]

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:

// Load documents stored locally creating a temporary synthetic key ID&Version

Old:

Load

autonumber(ID&Version) as Old.IDVersion,

ID as Old.ID,

    Version as Old.Version,

    LastVersion as Old.LastVersion,

    Date(RecordDate) as Old.RecordDate,

    Date(LastUpdated) as Old.LastUpdated

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', '30/01/2023'

];

// Find Max(LastUpdated) date

Temp:

Load Max(Old.LastUpdated) as MaxDate

Resident Old;

LET vMaxDate = Peek('MaxDate',0);

Trace $(vMaxDate);

Drop Table Temp;

// Load rows that were updated: LastUpdated>$(vMaxDate)

New:

Load 

autonumber(ID&Version) as New.IDVersion,

ID as New.ID,

    Version as New.Version,

    LastVersion as New.LastVersion,

    Date(RecordDate) as New.RecordDate,

    Date(LastUpdated) as New.LastUpdated

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', '30/01/2023'

3, 1, 'Y', '01/01/2024', '01/01/2024'

1, 5, 'N', '01/01/2019', '01/01/2024'

1, 6, 'Y', '01/01/2019', '30/01/2024'

]

Where LastUpdated>$(vMaxDate);

// Add rows already in old table

Concatenate(New)

Load

Old.IDVersion as New.IDVersion,

Old.ID as New.ID,

    Old.Version as New.Version,

    Old.LastVersion as New.LastVersion,

    Old.RecordDate as New.RecordDate,

    Old.LastUpdated as New.LastUpdated

Resident Old

Where Not Exists(New.IDVersion, Old.IDVersion);

// Drop temporary synthetic key column

Drop Field New.IDVersion;

// Drop unused table

Drop Table Old;