Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
aaronnayan
Contributor 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
dilipranjith
Honored Contributor

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

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

store into qvd

end if
Vegar
Valued Contributor III

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

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

Please ekskuse my Norglish and Swenglish typos.
aaronnayan
Contributor III

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

Hi Dilipranjith,

Thanks for your suggestion but sadly does not work. Smiley Sad

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
Valued Contributor III

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

It's probably a typo Day(Today)=1 should be Day(Today())=1
Please ekskuse my Norglish and Swenglish typos.
aaronnayan
Contributor III

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

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
Valued Contributor III

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

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
Please ekskuse my Norglish and Swenglish typos.
Vegar
Valued Contributor III

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

 

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?

 

Please ekskuse my Norglish and Swenglish typos.
aaronnayan
Contributor III

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

Hi Sorry,

 

This does not work in QV 11.2 unfortunatley

 

Aaron

aaronnayan
Contributor III

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

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