topic Re: How can I define these variables in the script? in QlikView

How can I define these variables in the script?

Mon, 07 Jan 2013 19:43:08 GMT

I would like to make a #Include script that returns these variabls:

vCurQtr = Text(Year(Today())+Max(CurYr)) & '-' & concat(CurrQtr)
cCurYrYr = Text(Year(Today())+Max(CurYr))
vQOQQtr = Text(Year(Today())+Max(PriorYr)) & '-' & concat(PriorQtr)
vPriorPriorQtr = Text(Year(Today())+Max(PriorPriorYr)) & '-' & concat(PriorPriorQrt)
vPriorPriorPriorQtr = Text(Year(Today())+Max(PriorPriorPriorYr)) & '-' & concat(PriorPriorPriorQrt)
vYOYQtr = Text(Year(Today())+Max(CurYr)-1) & '-' & concat(CurrQtr)
vCurQOQYOY_Qtr = '(' & vCurQtr & ' | ' & vQOQQtr & ')'
vRollingQtrs_4 = '(' & vCurQtr & ' | ' & vQOQQtr & ' | ' & vPriorPriorQtr & ' | ' & vPriorPriorPriorQtr & ')'
vCurYr = '(' & vCurYrYr & '-Q1 | ' & vCurYrYr & '-Q2 | ' & vCurYrYr & '-Q3 | ' & vCurYrYr & '-Q4)'

Right now I have had to define these in the Variable Overview so I would have to define these on every document that uses these variables. Is there a way to make this a script that loads my table (see attached QlikView and XLS) and then creates these variables and then drops the table?

Thank you very much!

Re: How can I define these variables in the script?

Mon, 07 Jan 2013 19:45:09 GMT

Why did this get marked as "Assumed Answered"? I just posted it.

Re: How can I define these variables in the script?

Anonymous — Mon, 07 Jan 2013 19:47:44 GMT

Hi, Try using "let" & "Set" function in Edit Script box while loading the data.

Re: How can I define these variables in the script?

Mon, 07 Jan 2013 19:51:37 GMT

I tried that but get line errors on all of the variables. Not really sure what I am doing wrong. It appears to have a problem with Peek and Concat both in the script... so how do I get the value from the table loaded into the variable?

Re: How can I define these variables in the script?

Anonymous — Mon, 07 Jan 2013 20:05:15 GMT

can you attach sample file in which you tried that?

Re: How can I define these variables in the script?

nagaiank — Mon, 07 Jan 2013 20:08:45 GMT

I think you need to define the variables to have the text used. For example,

vCurQtr = 'Text(Year(Today())+Max(CurYr))' & Chr(38) & '-' & Chr(38) & 'Concat(currQtr)'

Re: How can I define these variables in the script?

Mon, 07 Jan 2013 20:13:56 GMT

I added DateAutomation2.qvw to the discussion... not sure how to add it here.

Re: How can I define these variables in the script?

Mon, 07 Jan 2013 20:19:43 GMT

My goal is to set the variables, not leave them dynamic... so were it to run today, it would say:

vCurQtr = 2013-Q1
cCurYrYr = 2013
vQOQQtr = 2012-Q4
vPriorPriorQtr = 2012-Q3
vPriorPriorPriorQtr = 2012-Q2
vYOYQtr = 2012-Q1
vCurQOQYOY_Qtr = (2013-Q1 | 2012-Q4)
vRollingQtrs_4 = (2013-Q1 | 2012-Q4 | 2012-Q3 | 2012-Q2)
vCurYr = (2013-Q1 | 2013-Q2 | 2013-Q3 | 2013-Q4)

Re: How can I define these variables in the script?

Anonymous — Mon, 07 Jan 2013 21:00:07 GMT

There are different ways of doing it. See if this helps you

let vtoday=today();

let vCurrQtr=QuarterName (today());

let vCurrYr=year(today());

let vPriorQ4=QuarterName(today()-90);

let vPriorQ3=QuarterName(today()-180);

let vPriorQ2=QuarterName(today()-270);

let vPriorQ1=QuarterName(today()-365);

let vCurQOQYOY_Qtr =QuarterName (today()) &'|'&QuarterName(today()-90);

Re: How can I define these variables in the script?

Mon, 07 Jan 2013 21:04:49 GMT

so how do I define that

"QuarterName(today())" returns 2013-Q1?

Re: How can I define these variables in the script?

Anonymous — Mon, 07 Jan 2013 21:07:22 GMT

Quartername is a inbuilt QlikView function. If you mandatorily have to have Q1 then you have to use mapping or you have to hardcode string.

Re: How can I define these variables in the script?

Mon, 07 Jan 2013 21:09:30 GMT

Basically from Nov 16th (2012) until Feb 15th (2013), we are in Fiscal Year 2013-Q1

Then Feb 16th -> May 15th is Q2

Then May 16th -> Aug 15th is Q3

Then Aug 15th -> Nov 15th is Q4

Re: How can I define these variables in the script?

Anonymous — Mon, 07 Jan 2013 21:19:46 GMT

I think defining your own calendar in Spreadsheet would be better. Load the spreadsheet and then you would be able to do whatever you want.

Re: How can I define these variables in the script?

Mon, 07 Jan 2013 22:16:27 GMT

Thank you for your help!

Here was my final script... used mapping:

SET

ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

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

//Load Reference Table

DtRefTable:

LOAD CurDt,

     CurQtr,

     CurYr,

     PrQtr,

     PrYr,

     Pr2Qtr,

     Pr2Yr,

     Pr3Qtr,

     Pr3Yr

FROM

DateMap.xlsx

(ooxml, embedded labels, table is DateToQtr);

//Load current Qtr Maps

CurQtrMap:

Mapping LOAD CurDt, CurQtr

resident DtRefTable;

CurYrMap:

Mapping LOAD CurDt, CurYr

resident DtRefTable;

//Load prior Qtr Maps

PrQtrMap:

Mapping LOAD CurDt, PrQtr

resident DtRefTable;

PrYrMap:

Mapping LOAD CurDt, PrYr

resident DtRefTable;

//Load Prior 2 Qtr Maps

Pr2QtrMap:

Mapping LOAD CurDt, Pr2Qtr

resident DtRefTable;

Pr2YrMap:

Mapping LOAD CurDt, Pr2Yr

resident DtRefTable;

//Load Prior 3 Qtr Maps

Pr3QtrMap:

Mapping LOAD CurDt, Pr3Qtr

resident DtRefTable;

Pr3YrMap:

Mapping LOAD CurDt, Pr3Yr

resident DtRefTable;

//define variables

let vDate = Text(Date(Today(), 'DD-MMM'));

let vCurYr = Year(Today());

let vCurQtr = Text (vCurYr + applymap('CurYrMap', vDate)) & '-' & applymap('CurQtrMap', vDate);

let vPrQtr = Text (vCurYr + applymap('PrYrMap', vDate)) & '-' & applymap('PrQtrMap', vDate);

let vPr2Qtr = Text (vCurYr + applymap('Pr2YrMap', vDate)) & '-' & applymap('Pr2QtrMap', vDate);

let vPr3Qtr = Text (vCurYr + applymap('Pr3YrMap', vDate)) & '-' & applymap('Pr3QtrMap', vDate);

let vPrYrQtr = Text (vCurYr + applymap('CurYrMap', vDate)-1) & '-' & applymap('CurQtrMap', vDate);

let vCurQtr_PrQtr = '(' & vCurQtr & '|' & vPrQtr & ')';

let vRollingQtrs_4 = '(' & vCurQtr & '|' & vPrQtr & '|' & vPr2Qtr & '|' & vPr3Qtr & ')';

let vCurYrQtrs = '(' & vCurYr & '-Q1 |' & vCurYr & '-Q2 |' & vCurYr & '-Q3 |' & vCurYr & '-Q4)';

//drop table and delete variables

Drop table DtRefTable;

//Example LET varname=null();