topic Re: Check for deleted data in App Development

Check for deleted data

steasy — Mon, 29 Jul 2024 14:00:04 GMT

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.

Re: Check for deleted data

ramonneto — Mon, 29 Jul 2024 14:53:12 GMT

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.

Re: Check for deleted data

marksouzacosta — Mon, 29 Jul 2024 14:54:55 GMT

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.