topic Re: Variables for each Month in the format DD-MMM-YYYY in QlikView

Variables for each Month in the format DD-MMM-YYYY

ivandrago — Fri, 15 May 2015 10:31:16 GMT

Hi,

I require six different variables that will show in the below format

01-May-2015

01-Apr-2015

01-Mar-2015

01-Feb-2015

01-Jan-2015

01-Dec-2014

Any ideas how to do this in the script?

Thanks

Re: Variables for each Month in the format DD-MMM-YYYY

amit_saini — Fri, 15 May 2015 10:37:10 GMT

Hi,

I'm not sure why you want to create the variable , better you can change format of your date filed.

Date(Your_date_Field ,'DD-MMM-YYYY') as Date

Thanks,
AS

Re: Variables for each Month in the format DD-MMM-YYYY

MarcoWedel — Fri, 15 May 2015 10:54:45 GMT

Hi,

one way to do so could be:

FOR i = 1 to 6
LET vDate$(i) = Date(AddMonths(MakeDate(2014,12),i-1),'DD-MMM-YYYY');
NEXT

but I agree that you should also describe what you intend to use those variables for.

Maybe there are other possibilities.

hope this helps

regards

Marco

Re: Variables for each Month in the format DD-MMM-YYYY

PrashantSangle — Fri, 15 May 2015 10:54:59 GMT

Hi,

What is your variable expression??

Regards

Re: Variables for each Month in the format DD-MMM-YYYY

Fri, 15 May 2015 14:10:56 GMT

Hi,

You can try this In Script.

Date(Date#(date_Field) ,'DD-MMM-YYYY') as Date

Re: Variables for each Month in the format DD-MMM-YYYY

ivandrago — Fri, 15 May 2015 19:17:10 GMT

Hi All,

I have a SQL Server Table which I want to query, I want it to loop through each SalesRunDate in the Where Clause that is equal to the variable and then produce a qvd file with the variable aspart of the qvd file name, i.e

//For each Variable Loop till last one

Sales_vDate1:

Load *,

vDate1 as [Sales Month];

SQL

Select *

From SalesTable

Where SalesRunDate = $(vDate1);

STORE Sales_vDate1 INTO c:\Sales_vDate1.qvd(QVD)

DROP Table Sales_vDate1

//Next Variable

Any ideas how to do this?

Thanks

Re: Variables for each Month in the format DD-MMM-YYYY

maxgro — Fri, 15 May 2015 21:02:35 GMT

using MarcoWedel‌ FOR

OLEDB CONNECT32 TO [Provider=SQLOLEDB.1;Persist Security Info=True;User ID=xxxx;Initial Catalog=xxxxxx;Data Source=xxxxxxx;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=xxxxxx;Use Encryption for Data=False;Tag with column collation when possible=False] (XPassword is xxxxxxxxxx);

FOR i = 1 to 6

LET vDate$(i) = Date(AddMonths(MakeDate(2014,12),i-1),'YYYYMMDD');

TRACE '$(vDate$(i))';

Table_$(i):

LOAD *;

SQL SELECT *

FROM "sisim_stat".dbo."d003_date"

where CONVERT(VARCHAR(10), dat_data, 112) = '$(vDate$(i))';

store Table_$(i) into Table_$(i).qvd (qvd);

drop table Table_$(i);

NEXT;

Re: Variables for each Month in the format DD-MMM-YYYY

ivandrago — Mon, 18 May 2015 09:57:06 GMT

Hi,

How do I put a folder of "C:\QlikVIew\Test\" and then put the QVD's in this location?

As I get the following error:

Table not found

store Table_1 into Table_1.qvd (qvd)

Thanks

Re: Variables for each Month in the format DD-MMM-YYYY

maxgro — Mon, 18 May 2015 10:17:26 GMT

replace E:\temp with your folder

set f='E:\temp';

FOR i = 1 to 6

LET vDate$(i) = Date(AddMonths(MakeDate(2014,12),i-1),'YYYYMMDD');

TRACE '$(vDate$(i))';

Table_$(i):

LOAD *;

SQL SELECT *

FROM "sisim_stat".dbo."d003_date"

where CONVERT(VARCHAR(10), dat_data, 112) = '$(vDate$(i))';

store Table_$(i) into $(f)\Table_$(i).qvd (qvd)

drop table Table_$(i);

NEXT;

Re: Variables for each Month in the format DD-MMM-YYYY

ToniKautto — Mon, 18 May 2015 10:21:08 GMT

That should indicate that Table_1 does not exist. I do not think the output target is an issue in this example.

Re: Variables for each Month in the format DD-MMM-YYYY

maxgro — Mon, 18 May 2015 10:39:46 GMT

if the problem is some ables don't exist you can add a check before the store and drop

LET a = alt(NoOfRows('Table_$(i)'),0);

if $(a)> 0 then

store Table_$(i) into $(f)\Table_$(i).qvd (qvd);

drop table Table_$(i);

endif;

Re: Variables for each Month in the format DD-MMM-YYYY

ivandrago — Mon, 18 May 2015 10:44:33 GMT

Hi,

How do I get the table names to show the Date is stead of the Number?

Thanks

Re: Variables for each Month in the format DD-MMM-YYYY

ivandrago — Mon, 18 May 2015 10:56:46 GMT

Sorted it

STORE Table_$(i) into $(f)\Table_$(vDate$(i)).qvd (qvd);

Re: Variables for each Month in the format DD-MMM-YYYY

ivandrago — Mon, 18 May 2015 11:26:28 GMT

Hi,

I changed the script to following

LET vDate$(i) = Date(AddMonths(MakeDate(2014,12),i-1),'DD-MMM-YYYY');

able_$(i):
Load
*,
$(vDate$(i)) as [Start Month];

SQL
SELECT

ut I get the first message of

Field not found - <Dec>

Any ideas?

Re: Variables for each Month in the format DD-MMM-YYYY

ivandrago — Mon, 18 May 2015 12:54:30 GMT

Sorted it, changed it to the following:

LET vDate$(i) = Date(AddMonths(MakeDate(2014,12),i-1),'DD-MMM-YYYY');

able_$(i):
Load
*,
'$(vDate$(i))' as [Start Month];

SQL
SELECT