Check for deleted data

[steasy]

Hi,

we have a table in our system and its entries are changing dynamically. We want to track the table from now on.

Is there a way to keep the data in a Qlik table and to check on which day they got deleted?

Furthermore it is possible, that the same ID returns after being deleted initially. In that case we want to show two datasets. One dataset with the old period and one dataset within the current period.

Is that somehow possible?

Thanks in advance.

[ramonneto · Accepted solution]

Hello @steasy ! I suggest creating a date field to record the day the ID was queried in a QVD, with "active" status, for example (this will be your history).

Something like:

"LOAD
ID,
Today() as date,
'active' as Status"

Then, in subsequent loads, compare the new data to
identify deletions (IDs present in the history, but absent in the current data).

In the same way, identify new entries and maintain 'active' status. You can do this using a WHERE NOT EXISTS or WHERE EXISTS.
Use concatenation to join deletions and new entries to the history.

[marksouzacosta]

Yes. It is possible. You have to use one of the many slowly changing dimension techniques. This topic is very broad, but this is the term you have to look for.