Create Field with Changed / Deleted Field

[Mahran]

Hi All,

I have an app with old and new data. The app checks if someone has changed or deleted data. If there is a change, then a FLAG is displayed. This app is loaded many times during the day.
The app runs according to the following scheme:

Old:
LOAD
A,
B,
C
FROM X

Outer Join
LOAD
A as A1,
B as B1,
C as C1
FROM Y

Changes:
NoConcatenate Load
*,
if (A <> A1, 'Flag', if(B <> B1, 'Flag', if(C <> C1, 'Flag'))) as Flag
Resident Old;
Drop Old;

The App is working fine, but now I want to have a new field that shows the date where the change occurred (that is, the last time it was loaded). I don't want this date to be overwritten again, so after each load I want exactly this date to remain.

Thanks

[marcus_sommer · Accepted solution]

I think I would tend to a bit different approach, like:

let vTime = now(); // initial statement

Old:
// load * // normal statement
load A, B, C, A & B as UniqueIDKey, A & B & C as UniqueIDandValueKey, '$(vTime)' as Time // initial statement
from Old.qvd;

let vTime = now();

concatenate(Old)
load A, B, C, A & B as UniqueIDKey, A & B & C as UniqueIDandValueKey, '$(vTime)' as Time
from New
where not exists(UniqueIDandValueKey, A & B & C);

store Old into Old.qvd;

This means already existing records won't be loaded again and new or changed ones would be added. Each record get a timestamp and with additionally measures like a join-load with max(Time) grouped by UniqueIDKey or with a record-counting with interrecord-functions you could flag the newest/oldest of a UniqueIDKey record or maybe ranking them.

- Marcus

[Mahran]

Hello,
thanks for your answer but this solution does not work in my case. I have more than 3 columns in my case and I can't create a key from them. That's why I chose my solution with the outer join.
The date is still overwritten on each load. Is there no way to say "if there is a value, then do nothing"?

[marcus_sommer]

The number of columns is in regard to the logic irrelevant but maybe not in regard to the performance. But the last will be true for all approaches whereby loading (optimized) from qvd's and checking any values with (not) exists() logic is very common in the Qlik world by larger data-sets and/or any requirements to apply incremental approaches. Of course this is often not trivial but I doubt that any other way would be easier or more performant.

Beside this it depends on your real data and requirements how many keys are needed and which fields might be included. Like hinted you will need an UniqueID on the record-level against which any potentially changes could be checked and then n fields in which changes are possible (usually not all).

In regard to your wanted date give the comments attention in my example - which differs within an initial load and then regular loads.  

- Marcus