Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

ivandrago
Contributor II

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

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

1 Solution

Accepted Solutions
MVP
MVP

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

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;


14 Replies
amit_saini
Honored Contributor III

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

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

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

QlikCommunity_Thread_164156_Pic1.JPG

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

Hi,

What is your variable expression??

Regards

Regards,
Prashant Sangle
Not applicable

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

Hi,

  You can try this In Script.

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

ivandrago
Contributor II

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

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

MVP
MVP

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

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
Contributor II

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

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

MVP
MVP

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

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;


Employee
Employee

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

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

Community Browser