Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
aaronnayan
Creator III
Creator III

Store Qvd's on 1st of every month with Date (Snapshot)

Hi Guys,

I need your help on my problem.

I have "Table1" that reloads and is stored into a qvd daily. I would like to copy "Table1" and store into a qvd again but on the first of every month..  (Similar to a snapshot)

?? IF first day of month , Store "Table1" to qvd. ??

I also would like to add to "Table1" the month it stores the data as a field if possible?

I.e

On the 1st April "Table1" is stored to (QVD)  April2019.qvd;  - including the field [StoreDate] =  01/04/19 for every row maybe?

April2019.qvd

May2019.qvd

June2019.qvd

 

10 Replies
dplr-rn
Partner - Master III
Partner - Master III

try something like below (rough code)
if (day(today)=1) then
Load *,
today() as StoreDate
resident yourtable;

store into qvd

end if
Vegar
MVP
MVP

I would suggest a more robust approach. What happens if you forget to run the script the 1th of the month or if the server is down? The script below will store the file the first time I its run during a month.

Let vMonth = text(date(today(), 'MMMMYYYY'));

[$(vMonth)]:
Load *,
today() as SnapshotDate
resident yourtable;

WHEN IsNull(QvdCreateTime('$(vMonth).QVD') store $(vMonth) into $(vMonth).QVD (qvd);

aaronnayan
Creator III
Creator III
Author

Hi Dilipranjith,

Thanks for your suggestion but sadly does not work. 😞

I have tried it using Day(Today)=11 and it does not generate a qvd. Do you have any other ideas?

 

Also could you advise on how to get the Year&months in the name of the qvd ?

2019April.qvd

2019May.qvd

Vegar
MVP
MVP

It's probably a typo Day(Today)=1 should be Day(Today())=1
aaronnayan
Creator III
Creator III
Author

Hi Vegar,

 

Thanks for your advice. 

I dont think the syntax is correct here -> WHEN IsNull(QvdCreateTime('$(vMonth).QVD') store $(vMonth) into $(vMonth).QVD (qvd);

 

Could you help please?

Vegar
MVP
MVP

The syntax looks ok (im on my cell so I can't check in a real environment), but you need to adjust both uses of '$(vMonth).QVD' with a LIB LIB:\\Destination\$(vMonth).QVD
Vegar
MVP
MVP

 

An simple example with an autogenerated table.  

Let vMonth = text(date(today(), 'MMMYYYY'));
[$(vMonth)]:
Load 
RecNo() as QVFactId,
today() As SnapshotDate
AutoGenerate 10;

WHEN IsNull(QvdCreateTime('$(vG.QVDPath)$(vMonth).QVD')) store $(vMonth) into '$(vG.QVDPath)$(vMonth).QVD' (qvd);

@aaronnayan  Do you get an errormessage using this script?

 

aaronnayan
Creator III
Creator III
Author

Hi Sorry,

 

This does not work in QV 11.2 unfortunatley

 

Aaron

aaronnayan
Creator III
Creator III
Author

Does anyone have any other advice on how to achieve this??