Store unique rows into QVD archive

[zet]

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 ?

[Anil_Babu_Samineni · Accepted solution]

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);

[zet]

vMaxDate returned null in my case

This is my qvd file (Datetime=utc_obs)

[zet]

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

😥

[Anil_Babu_Samineni · Accepted solution]

try this

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

[zet]

Perfectly works !!

Happy new year !!