topic Set Date Value in a Variable in QlikView

Set Date Value in a Variable

Mon, 30 May 2011 11:59:20 GMT

Hi,

I want to set a sysdate in a variable so that I can concat that variable with some prefix strings as a filename on qvd generation ?

Please suggest me idea on how to do that ?

Regards,

Sachin A.

Set Date Value in a Variable

Mon, 30 May 2011 12:07:47 GMT

Hi,

Use today()

Set Date Value in a Variable

Mon, 30 May 2011 12:14:19 GMT

Hi a.medina,

Please look the following the script..

SET ThousandSep=',';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

SET vDate = today();

LoadFirst:

LOAD Serial,

Value

FROM

[.\DemoTable.xlsx]

(ooxml, embedded labels, table is Sheet1);

-----------------------------------------------------------------------------------------------------

Now I want to save the table in LoadFirst Section into a QVD file, whose name will be like this..

QVD_vDate.qvd

I want to use that value in qvd name creation..

How to achieve this ?

Thanks...

Re: Set Date Value in a Variable

brenner_martina — Mon, 30 May 2011 12:16:58 GMT

Hi,

The variable is wrong, you need LET not SET:

Let vDate = Today();

then you can enter $() like this:

Store LoadFirst Into QVD_$(vDate).qvd;

Set Date Value in a Variable

Mon, 30 May 2011 12:17:12 GMT

Hi Sachin,

Do like this

STORE * FROM LoadFirst INTO LoadFirst_$(vDate).QVD;

Set Date Value in a Variable

Mon, 30 May 2011 12:32:35 GMT

Hi,

Its works.

Store * from LoadFirst into .\QVD_$(vDate).qvd

Thanks

...

Will you please tell me how to drop a QVD created on (today()-1) day automatically ?

Set Date Value in a Variable

Mon, 30 May 2011 12:35:28 GMT

if you want to drop the table

After storing just use this syntax for the table you used

DROP TABLE LoadFirst;

Set Date Value in a Variable

Mon, 30 May 2011 12:38:38 GMT

Hi Sravan,

I want to drop/delete QVD from Script, is that possible ? Table dropping can be done but i m not sure about qvd.

Thanks.

Set Date Value in a Variable

Mon, 30 May 2011 12:42:32 GMT

Sachin you have already stored the QVD in the directory where your .qvw is present.

when you have already stored it, I dont know now what you mean by deleting the QVD. you can do it manually if you dont want it.

Set Date Value in a Variable

Mon, 30 May 2011 12:56:39 GMT

No mate. See.. I just took an example , In real scenarios, lots of qvd will create on the daily basis with a retention period of X days so, I want to delete already created qvd files which croses the X days of retention automatically.. Did you get my point ?

Set Date Value in a Variable

spsrk_84 — Mon, 30 May 2011 13:05:47 GMT

Hi,

LET vDate=Date(Today(),'DD-MM-YYYY');

LoadFirst:

LOAD

Serial,

Value

From [.\DemoTable.xlsx](ooxml,embedded labels,table is sheet1);

Store Loadfirst into Qvd_'$(vDate)'.qvd;

Try in this way ..

Rregards,

Ajay

Set Date Value in a Variable

stephencredmond — Mon, 30 May 2011 13:18:56 GMT

Hi,

First, when setting my variable, I would format as ISO. Either using:

let vDate = TimeStamp(Now(), 'YYYYMMDDhhmmss');

let vDate = Date(Now(), 'YYYYMMDD');

That way, when the file is listed, it will sort alphabetically in date order.

You will need to write a VBScript to delete any older files.

Regards,

Stephen

Re: Set Date Value in a Variable

Tue, 29 Jul 2014 19:32:31 GMT

Try this solution, it really helped me out!

Using a QlikView variable as a SQL “function” during load | Walter Shpuntoff on QlikView and Saleslogix Visual Analyzer