Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
zet
Contributor III
Contributor III

Store unique rows into QVD archive

Hello,

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

DatetimeDateTimeQuantity
2020-12-29:00:002020-12-2900:002
2020-12-29:00:012020-12-2900:012
2020-12-29:00:022020-12-2900:022

 

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 ?

 

2 Solutions

Accepted Solutions
Anil_Babu_Samineni

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

View solution in original post

Anil_Babu_Samineni

try this

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

View solution in original post

5 Replies
Anil_Babu_Samineni

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

View solution in original post

zet
Contributor III
Contributor III
Author

vMaxDate returned null in my case

This is my qvd file (Datetime=utc_obs)

zet
Contributor III
Contributor III
Author

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

😥

Anil_Babu_Samineni

try this

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

View solution in original post

zet
Contributor III
Contributor III
Author

Perfectly works !!

Happy new year !!