Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
May 18th 10AM ET, Live Chat, bring your QlikView questions. 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 !!