topic Store unique rows into QVD archive in QlikView

Store unique rows into QVD archive

zet — Sat, 16 Nov 2024 17:39:44 GMT

Hello,

I have a qvd that I want to fill with new data every day with those kind of data:

Datetime	Date	Time	Quantity
2020-12-29:00:00	2020-12-29	00:00	2
2020-12-29:00:01	2020-12-29	00:01	2
2020-12-29:00:02	2020-12-29	00:02	2

So I'm using this script

Archive:
Load * from $(vQVD)\Archive.qvd(qvd);
Concatenate
Load * from $(vQVD)\NewData.qvd(qvd);
store Archiveinto $(vQVD)\Archive.qvd(qvd);

But when I run the script tmore than one time with the same datetime, quantity multiplied and that's what I want to avoid.

So I thought of maybe getting the max of datetime from new data like this :

maxDatetime=Load max(Datetime) from $(vQVD)\NewData.qvd(qvd);

Archive:
Load * from $(vQVD)\Archive.qvd(qvd);
Concatenate
Load * from $(vQVD)\NewData.qvd(qvd) where Datetime>maxDatetime;
store Archiveinto $(vQVD)\Archive.qvd(qvd);

but this doesn't work because of the timestamp format.

Any solution to maintain unique date and time fields into qvd ?

Re: Store unique rows into QVD archive

Anil_Babu_Samineni — Wed, 30 Dec 2020 16:39:55 GMT

Perhaps this way

Archive:
Load * from $(vQVD)\Archive.qvd(qvd);

Max:

Load Max(Datetime) as Max_Datetime;

Let vMaxDate=Peek('Max_Datetime', 0, 'Max');

Drop Table Archive;

Archive:
Load * from $(vQVD)\Archive.qvd(qvd);
Concatenate
Load * from $(vQVD)\NewData.qvd(qvd) where TimeStamp(Datetime)>TimeStamp($(vMaxDate));

store Archive into $(vQVD)\Archive.qvd(qvd);

Re: Store unique rows into QVD archive

zet — Wed, 30 Dec 2020 17:27:09 GMT

vMaxDate returned null in my case

This is my qvd file (Datetime=utc_obs)

Re: Store unique rows into QVD archive

zet — Thu, 31 Dec 2020 13:23:57 GMT

I added resident Archive to make it run but the comparison didn't work between timestamp(utc_obs) et timestamp($(vMaxDate))

😥

Re: Store unique rows into QVD archive

Anil_Babu_Samineni — Thu, 31 Dec 2020 13:36:30 GMT

try this

where Num(Datetime)>Num('$(vMaxDate)')

Re: Store unique rows into QVD archive

zet — Thu, 31 Dec 2020 14:11:11 GMT

Perfectly works !!

Happy new year !!