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

[ivandrago]

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

[amit_saini]

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

[MarcoWedel]

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

[PrashantSangle]

Hi,

What is your variable expression??

Regards

[#5]

Hi,

  You can try this In Script.

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

[ivandrago]

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

[maxgro]

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;

[ivandrago]

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

[maxgro · Accepted solution]

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;

[ToniKautto]

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