topic Archiving logic - not yet working - plz help in QlikView

Archiving logic - not yet working - plz help

datanibbler — Mon, 17 Oct 2016 09:39:03 GMT

Hi,

I have a script to compute the current usage of available storage capacities.

This report can potentially be run at any time - but it should archive exactly one value per day, always at the ame time, inbetween the early and late shift (at 1:45 pm or a bit later). At that time, the report is run via the QMC and then it should check whether a record for the current day is already in the existing history_file, and if not, it should concatenate one and save it again.

For some reason that logic does not yet work. Maybe someone here can help me:

The code is the following:

// Jetzt laden wir erst mal die bestehende Historiendatei:
NoConcatenate
Hist_old:
LOAD
Datum_Hist,
Regalreihe_Hist,
Werk_Hist,
Bereich_Hist,
Gesamtkapazität_Hist,
Beladung_Hist
FROM
$(v_Pfad_Lager_Hist_74_qvd) // This variable holds the path to the history.qvd which I have put together before this
(qvd);

// Um überprüfen zu können, ob wir für heute schon einen Wert haben - es soll ja nur einen Auslastungs-Wert pro Tag geben - brauchen wir
// das heutige Datum und schauen einfach den untersten Eintrag in der Tabelle an, die ja chronologisch sortiert ist - wenn der unterste Eintrag
// das heutige Datum hat, dann gibt es da eben schon Daten, ansonsten hängen wir den neuen Eintrag an.
Let v_today = Today();

LET v_concat = 0; // Wir setzen als Standard erst mal, dass wir nix machen.

IF Now() >= Maketime(13, 45, 00) THEN // Der Report läuft, über die QMC gesteuert, zum Schichtwechsel und speichert da jew. den Lagerauslastungs-Status weg. Wenn es
// noch früher ist als das, dann prüfen wir hier noch gar nix. Damit bleibt die Variable auf 0, wir hängen keine neuen Daten an.
IF PEEK('Datum_Hist', -1, 'Hist_old') = '$(v_today)' THEN
v_concat = 0
ELSE
v_concat = 1
ENDIF
ENDIF

// Wenn diese Variable den Wert 1 hat, dann hängen wir die neue Datei an die alte dran.
IF v_concat = 1 THEN
Concatenate (Hist_old)
LOAD
Hist_new.Datum_Hist as Datum_Hist,
Hist_new.Regalreihe_Hist as Regalreihe_Hist,
Hist_new.Werk_Hist as Werk_Hist,
Hist_new.Bereich_Hist as Bereich_Hist,
Hist_new.Gesamtkapazität_Hist as Gesamtkapazität_Hist,
Hist_new.Beladung_Hist as Beladung_Hist
RESIDENT Hist_new;
DROP TABLE Hist_new;
ENDIF

// Jetzt ist die Historiendatei aktualisiert, jetzt schreiben wir sie wieder weg.

IF v_concat = 1 THEN // Wenn wir keine neuen Daten angehängt haben, dann brauchen wir auch keine neue qvd, denn die liegt ja schon da.
STORE Hist_old INTO $(v_Pfad_Lager_Hist_74_qvd);
ENDIF

For some reason, the first IF_THEN clause (the comparison with the time) does not work - it is now 11:30 am, but still the condition returned true - in Debugging_mode, the cursor entered that clause and computed the inner one.

What is wrong there?

Thanks a lot!

Best regsards,

DataNibbler

P.S.: It seems to be working in general - there are the dates from Oct 10 to today in the file, a varying nr. of records for each day - that looks all right. Only the file has a change_time from today at about 9am which should not be the case - I rasn the report locally at that time all right, but the history_file should only be updated at 1:45 pm like the code says ...

Re: Archiving logic - not yet working - plz help

marcus_sommer — Mon, 17 Oct 2016 11:25:18 GMT

Hi DataNibbler,

you are comparing a timestamp with a time ... therefore try:

IF frac(Now()) >= Maketime(13, 45, 00) THEN

- Marcus

Re: Archiving logic - not yet working - plz help

datanibbler — Tue, 18 Oct 2016 06:37:26 GMT

Hi Marcus,

thanks! yes, I noticed that yesterday. I tried with TIME(NOW()) - that seems to work, at least on the GUI it did.

Frac(Now()) seems to be a lot more precise, I put that into the code for now. Let's see.

Re: Archiving logic - not yet working - plz help

marcus_sommer — Tue, 18 Oct 2016 07:04:32 GMT

Hi DataNibbler,

time() will only format the string-representation but the numerical value is yet a timestamp - therefore frac() to split the time-part from the timestamp.

- Marcus

Re: Archiving logic - not yet working - plz help

datanibbler — Tue, 18 Oct 2016 07:22:02 GMT

Hi Marcus,

there seems to have been another problem - or maybe it was that: In a copy that I am working on, I was using TIME(NOW()) instead of frac. It is now 09:20am, I just ran the report and that history.qvd was obviously updated ...

I could now remove today's data from the history.qvd again, let's see.

Can you spot something else or is it really that part that made the whole thing misbehave?

Thanks a lot!

Best regards,

DataNibbler

I'll just try it out - I just removed today's data again and replaced my TIME() with frac() and now I'll run it again ...

Seems to work. The timestamp stayed the same, seemingly no data was added.

Re: Archiving logic - not yet working - plz help

marcus_sommer — Tue, 18 Oct 2016 10:43:33 GMT

Hi DataNibbler,

there is another point which might cause an issue - the fact that you pulled the date from the last record from the history-table to compare it with today. If your history-data not always ordered chronologically it might fail.

Beside them I could imagine that it might be more useful to use a where exists(Datum) clause instead of checking the date with peek and further that avoiding qualifying the fields made the load- and table-handling easier (maybe a filebasename() or 'current' / 'history' as Source could solve the purpose to keep an overview which data comes from which source respectively to access them over these field-flags).

- Marcus

Re: Archiving logic - not yet working - plz help

datanibbler — Tue, 18 Oct 2016 11:57:27 GMT

Hi Marcus,

you're right. If the history.qvd is not chronologically sorted, then PEEK() might cause issues of course - but why should it not be? Every day's new data is appended to the end of the history_table, so - given no one messes around with that table - it should always be correct.

I could of course check the existence of today's date in the table first thing and if it is already there, all the ado about that >>Hist_new<< would not be necessary - but it seems to work and it's not much given the thing runs on the server ...

Just now, both historization files were updated. So there is no acute necessity to change anything.

Best regards,

DataNibbler