How can I define these variables in the script?

[#1]

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!

[Anonymous · Accepted solution]

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.

[#3]

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

[Anonymous]

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.

[#5]

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();