Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
its_rajvir
Creator
Creator

Working of Subroutines.

Dear Experts,

Can anyone help me and explain how to use subroutines with example.  I checked the example on Qlik help website but still unable to understand its working properly.

Thankyou

 

2 Solutions

Accepted Solutions
Mark_Little
Luminary
Luminary

HI @its_rajvir 

Basically a subroutine allows you to write script and then run it when required instead of the just running down from top to bottom. The most common use of subroutine is i have used are

- If statement - if you only need to run script under certain criteria

- Reusing script - Loops or multiple joins. 

I am sure their are lot of other reasons.

I have used it in my Calendar script i reuse, to allow me to easier create a standard calendar, fiscal calendar or both. 

/*************************************[Calendar]**************************************/

//Variables for the table and date fields to link to your Data.
SET vDateField ='Date'; //Change value to the field name of your date you are linking on
SET vTableName = 'DATA'; //Change to the table name where your date field is located


//Variables to turn on routines
SET vFinancial = 1; //Replace for 1 or 0 to turn on or off the financial Calender
SET vWeekOS = 0; //Replace for 1 or 0 to turn on or off the financial week start off set


// Checks the above variables are set to 1 if not resets to 0.
LET vFinancial = IF($(vFinancial) <> 1, 0, $(vFinancial));
LET vWeekOS = IF($(vWeekOS) <> 1, 0, $(vWeekOS));

//These variables are used for your date off sets i.e. Financial Year
SET vFinYearOS = -3; //Replace this for the Month number of your financial start date i.e. 3 = April, As offset keep as a minus figure
SET vFinWeekOS = 1; //Replace this with off set for you finanical week start i.e. -1 will start the week on Sunday


Range:
LOAD
min($(vDateField)) as startdate, //Replace "Your Date" with the date field name, get the minimum date in the table
max($(vDateField)) as enddate //Replace "Your Date" with the date field name, get the maximum date in the table
resident $(vTableName); //Repoint at your table

//Peek out the values in to variables for later use
let vStart = peek('startdate',-1,'Range')-1;
let vEnd = peek('enddate',-1,'Range');
let vRange = $(vEnd) - $(vStart);

TRACE $(vStart);
TRACE $(vEnd);
TRACE $(vRange);

//Remove Range table as no longer needed
Drop table Range;

//Generate a table with a row per date between the range above
TempCalendar:
Load
$(vStart)+recno() as TempDate
autogenerate $(vRange);

//Creates the Sub Routine to create a the standard calendar
SUB Calendar
MasterCalendar:
LOAD *,
IF($(vWeekOS) =1, //If there Week start off set
DIV($(vDateField)-CAL_WEEK_START,7)+1,
WEEK($(vDateField))) AS CAL_WEEK;
LOAD *,
DATE(YEARSTART($(vDateField))-WEEKDAY(YEARSTART($(vDateField)))-$(vFinWeekOS)) AS CAL_WEEK_START;
LOAD
DATE(TempDate) AS $(vDateField),
NUM(TempDate) AS $(vDateField)_NUM,
/**Script to make needed dates based on the Calendar Year**/
YEAR(TempDate) AS CAL_YEAR,
CAPITALIZE(REPLACE(MONTH(TempDate),'.',''))&'-'&YEAR((TempDate)) AS CAL_MONTH_YEAR,
MONTH(TempDate) AS CAL_MONTH,
NUM(MONTH(TempDate)) AS CAL_MONTH_NO,
WEEKDAY(TempDate) AS CAL_WEEKDAY,
DAY(TempDate) AS CAL_DAY,
DUAL('Q' & CEIL(MONTH(TempDate)/3), CEIL(MONTH(TempDate)/3)) AS CAL_QUARTER,
'Q' & CEIL(MONTH(TempDate)/3) AS CAL_QUARTER_DESC,
CEIL(MONTH(TempDate) /3) AS CAL_QUARTER_NO,

/**Script to make flags for use in set analysis**/

IF(YEAR(TempDate)=YEAR(TODAY(1)),1) AS F_CAL_CURRENT_YEAR,
IF(CEIL(MONTH(TempDate)/3) = CEIL(MONTH(TODAY(1))/3) AND YEAR(TempDate) = YEAR(TODAY(1)), 1) AS F_CAL_CURRENT_QUARTER,
IF(CEIL(MONTH(TempDate)/3) = CEIL(MONTH(TODAY(1))/3) AND YEAR(TempDate) = YEAR(TODAY(1)) AND DATE(TempDate) >= TODAY(1),1) AS F_CAL_QTD,
IF(MONTH(TempDate)= MONTH(TODAY(1)) AND YEAR(TempDate)=YEAR(TODAY(1)),1) AS F_CURRENT_MONTH,
IF(WEEK(TempDate)= WEEK(TODAY(1)) AND YEAR(TempDate)=YEAR(TODAY(1)),1) AS F_CURRENT_WEEK,
IF(TempDate = TODAY(1)-1, 1) AS F_YESTERDAY_FLAG,
IF(YEAR(TempDate)=YEAR(TODAY(1))-1,1) AS F_CAL_PRIOR_YEAR,
IF(CEIL(MONTH(TempDate)/3) = CEIL(MONTH(ADDMONTHS(TODAY(1),-3))/3) AND YEAR(TempDate) = YEAR(TODAY(1)), 1) AS F_PRIOR_QUARTER,
IF(MONTH(TempDate)= MONTH(ADDMONTHS(TODAY(1),-1)) AND YEAR(TempDate)=YEAR(ADDYEARS(TODAY(1),-1)),1) AS F_PRIOR_MONTH,
IF(WEEK(TempDate)= WEEK(TODAY(1)-7) AND YEAR(TempDate)=YEAR(ADDYEARS(TODAY(1),-1)),1) AS F_PRIOR_WEEK,
IF(YEAR(TempDate)= YEAR(TODAY(1)) AND TempDate <= TODAY(1),1) AS F_CAL_YTD,
IF(YEAR(TempDate)= YEAR(TODAY(1)) AND MONTH(TempDate)= MONTH(TODAY(1)) AND TempDate <= TODAY(1),1) AS F_MTD,
IF(CEIL(MONTH(TempDate)/3) = CEIL(MONTH(TODAY(1))/3) AND YEAR(TempDate) = YEAR(ADDYEARS(TODAY(1),-1)), 1) AS F_CAL_QUATER_LY,
IF(CEIL(MONTH(TempDate)/3) = CEIL(MONTH(TODAY(1))/3) AND YEAR(TempDate) = YEAR(ADDYEARS(TODAY(1),-1)) AND DATE(TempDate) >= ADDYEARS(TODAY(1),-1),1) AS F_CAL_QTD_LY,
IF(MONTH(TempDate) = MONTH(ADDYEARS(TODAY(1),-1)),1) AS F_MONTH_LY,
IF(TempDate = ADDYEARS(TODAY(1),-1),1) AS F_DAY_LY,
IF(YEAR(TempDate)= YEAR(ADDYEARS(TODAY(1),-1)) AND TempDate <= ADDYEARS(TODAY(1),-1),1) AS F_CAL_LYD,
IF(YEAR(TempDate)= YEAR(ADDYEARS(TODAY(1),-1)) AND MONTH(TempDate)= MONTH(ADDYEARS(TODAY(1),-1)) AND TempDate <= ADDYEARS(TODAY(1),-1),1) AS F_LYMD,
IF(TempDate <= TODAY(1) AND TempDate >= (TODAY(1)-7),1) AS F_ROLLING_7,
IF(TempDate <= TODAY(1) AND TempDate >= (TODAY(1)-30),1) AS F_ROLLING_30,
IF(TempDate <= TODAY(1) AND TempDate >= (TODAY(1)-60),1) AS F_ROLLING_60,
IF(TempDate <= TODAY(1) AND TempDate >= (TODAY(1)-90),1) AS F_ROLLING_90,
IF(TempDate <= TODAY(1) AND TempDate >= (TODAY(1)-180),1) AS F_ROLLING_180,
IF(TempDate <= TODAY(1) AND TempDate >= (TODAY(1)-365),1) AS F_ROLLING_365,
IF(TempDate <= MONTHSTART(TODAY(1)) AND TempDate >= ADDMONTHS(MONTHSTART(TODAY(1)),-12),1) AS F_ROLLING_12_MONTHS

Resident TempCalendar
Order By TempDate ASC;

ENDSUB

//Creates the Sub Routine to create a the Financial calendar
SUB Financial

FinancialCalendar:
LOAD *,
IF($(vWeekOS) =1, //If there Week start off set
DIV($(vDateField)-FIN_WEEK_START,7)+1,
WEEK(ADDMONTHS($(vDateField),$(vFinYearOS)))) AS FIN_WEEK;
LOAD *,
APPLYMAP('FinWeekMap',FIN_YEAR_START) AS FIN_WEEK_START;
LOAD
DATE(TempDate) AS $(vDateField),
NUM(TempDate) AS $(vDateField)_NUM,
/**Script to make needed dates based on the Fiscal Year**/

YEAR(ADDMONTHS(TempDate,$(vFinYearOS)))&'/'&YEAR(ADDYEARS(ADDMONTHS(TempDate,$(vFinYearOS)),1)) AS FIN_YEAR,
YEAR(ADDMONTHS(TempDate,$(vFinYearOS))) AS FIN_YEAR_START,
NUM(MONTH(ADDMONTHS(TempDate,$(vFinYearOS))))&'-'&YEAR((ADDMONTHS(TempDate,$(vFinYearOS)))) AS FIN_MONTH_YEAR,
NUM(MONTH(ADDMONTHS(TempDate,$(vFinYearOS)))) AS FIN_MONTH_NO,
DAY(ADDMONTHS(TempDate,$(vFinYearOS))) AS FIN_DAY,
DUAL('Q' & CEIL(MONTH(ADDMONTHS(TempDate,$(vFinYearOS)))/3), CEIL(MONTH(ADDMONTHS(TempDate,$(vFinYearOS)))/3)) AS FIN_QUARTER,
'Q' & CEIL(MONTH(ADDMONTHS(TempDate,$(vFinYearOS)))/3) AS FIN_QUARTER_DESC,
CEIL(MONTH(ADDMONTHS(TempDate,$(vFinYearOS)))/3) AS FIN_QUARTER_NO,

/**Script to make flags for use in set analysis**/

IF(YEAR(ADDMONTHS(TempDate,$(vFinYearOS)))= YEAR(ADDMONTHS(TODAY(1),$(vFinYearOS))),1) AS F_FIN_CURRENT_YEAR,
IF(YEAR(ADDMONTHS(TempDate,$(vFinYearOS)))=YEAR(ADDMONTHS(TODAY(1),$(vFinYearOS)))-1,1) AS F_FIN_PRIOR_YEAR,
IF(YEAR(ADDMONTHS(TempDate,$(vFinYearOS)))= YEAR(TODAY(1)) AND TempDate <= TODAY(1),1) AS F_FIN_YTD,
IF(YEAR(ADDMONTHS(TempDate,$(vFinYearOS)))= YEAR(ADDMONTHS(TODAY(1),$(vFinYearOS))) AND TempDate <= ADDYEARS(TODAY(1),-1),1) AS F_FIN_LYD,

IF(CEIL(MONTH(ADDMONTHS(TempDate,$(vFinYearOS)))/3) = CEIL(MONTH(ADDMONTHS(TODAY(1),$(vFinYearOS)))/3)
AND YEAR(ADDMONTHS(TempDate,$(vFinYearOS))) = YEAR(ADDMONTHS(TODAY(1),$(vFinYearOS))), 1) AS F_FIN_CURRENT_QUARTER,
IF(CEIL(MONTH(ADDMONTHS(TempDate,$(vFinYearOS)))/3) = CEIL(MONTH(ADDMONTHS(TODAY(1),$(vFinYearOS)))/3)
AND YEAR(ADDMONTHS(TempDate,$(vFinYearOS))) = YEAR(ADDMONTHS(TODAY(1),$(vFinYearOS)))
AND DATE(ADDMONTHS(TempDate,$(vFinYearOS))) >= ADDMONTHS(TODAY(1),$(vFinYearOS)),1) AS F_FIN_QTD,
IF(CEIL(MONTH(ADDMONTHS(TempDate,$(vFinYearOS)))/3) = CEIL(MONTH(ADDMONTHS(ADDMONTHS(TODAY(1),$(vFinYearOS)),-3))/3)
AND YEAR(ADDMONTHS(TempDate,$(vFinYearOS))) = YEAR(ADDMONTHS(TODAY(1),$(vFinYearOS))), 1) AS F_FIN_PRIOR_QUARTER,
IF(CEIL(MONTH(ADDMONTHS(TempDate,$(vFinYearOS)))/3) = CEIL(MONTH(ADDMONTHS(TODAY(1),$(vFinYearOS)))/3)
AND YEAR(ADDMONTHS(TempDate,$(vFinYearOS))) = YEAR(ADDYEARS(ADDMONTHS(TODAY(1),$(vFinYearOS)),-1)), 1) AS F_FIN_QUATER_LY,
IF(CEIL(MONTH(ADDMONTHS(TempDate,$(vFinYearOS)))/3) = CEIL(MONTH(ADDMONTHS(TODAY(1),$(vFinYearOS)))/3)
AND YEAR(ADDMONTHS(TempDate,$(vFinYearOS))) = YEAR(ADDYEARS(ADDMONTHS(TODAY(1),$(vFinYearOS)),-1))
AND DATE(ADDMONTHS(TempDate,$(vFinYearOS))) >= ADDYEARS(ADDMONTHS(TODAY(1),$(vFinYearOS)),-1),1) AS F_FIN_QTD_LY
Resident TempCalendar
Order By TempDate ASC;

ENDSUB

IF $(vFinancial) = 0 THEN //If financial calendar is is off then just build a normal calendar
CALL Calendar;

ELSE //Created both normal calendar and financial

CALL Calendar;
CALL Financial;

NOCONCATENATE
Calendar:
LOAD
*
RESIDENT MasterCalendar;

LEFT JOIN (Calendar)

LOAD
*
RESIDENT FinancialCalendar;

DROP TABLES MasterCalendar, FinancialCalendar;

ENDIF;

DROP TABLE TempCalendar;

EXIT SCRIPT;

View solution in original post

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

A common use of subroutines is when you are repeating a block of code.  Variations in the code can be handled with parameters. For example, here is a Sub and Call statements to extract SQL tables and store to QVD.  On each call the value of the tablename (e.g. "Product") is placed in the variable "_table".

// Extract Subroutine. Parameter is SQL tablename.
SUB Extract (_table)
  [$(_table)]:
  SQL SELECT *
  FROM $(_table);
  STORE [$(_table)] INTO [$(_table)_Extract.qvd] (qvd);

  DROP TABLE [$(_table)];

ENDSUB // End of Subroutine

CALL Extract ('Product')
CALL Extract ('SalesOrderHeader')
CALL Extract ('SalesOrderDetail') 

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

View solution in original post

4 Replies
Mark_Little
Luminary
Luminary

HI @its_rajvir 

Basically a subroutine allows you to write script and then run it when required instead of the just running down from top to bottom. The most common use of subroutine is i have used are

- If statement - if you only need to run script under certain criteria

- Reusing script - Loops or multiple joins. 

I am sure their are lot of other reasons.

I have used it in my Calendar script i reuse, to allow me to easier create a standard calendar, fiscal calendar or both. 

/*************************************[Calendar]**************************************/

//Variables for the table and date fields to link to your Data.
SET vDateField ='Date'; //Change value to the field name of your date you are linking on
SET vTableName = 'DATA'; //Change to the table name where your date field is located


//Variables to turn on routines
SET vFinancial = 1; //Replace for 1 or 0 to turn on or off the financial Calender
SET vWeekOS = 0; //Replace for 1 or 0 to turn on or off the financial week start off set


// Checks the above variables are set to 1 if not resets to 0.
LET vFinancial = IF($(vFinancial) <> 1, 0, $(vFinancial));
LET vWeekOS = IF($(vWeekOS) <> 1, 0, $(vWeekOS));

//These variables are used for your date off sets i.e. Financial Year
SET vFinYearOS = -3; //Replace this for the Month number of your financial start date i.e. 3 = April, As offset keep as a minus figure
SET vFinWeekOS = 1; //Replace this with off set for you finanical week start i.e. -1 will start the week on Sunday


Range:
LOAD
min($(vDateField)) as startdate, //Replace "Your Date" with the date field name, get the minimum date in the table
max($(vDateField)) as enddate //Replace "Your Date" with the date field name, get the maximum date in the table
resident $(vTableName); //Repoint at your table

//Peek out the values in to variables for later use
let vStart = peek('startdate',-1,'Range')-1;
let vEnd = peek('enddate',-1,'Range');
let vRange = $(vEnd) - $(vStart);

TRACE $(vStart);
TRACE $(vEnd);
TRACE $(vRange);

//Remove Range table as no longer needed
Drop table Range;

//Generate a table with a row per date between the range above
TempCalendar:
Load
$(vStart)+recno() as TempDate
autogenerate $(vRange);

//Creates the Sub Routine to create a the standard calendar
SUB Calendar
MasterCalendar:
LOAD *,
IF($(vWeekOS) =1, //If there Week start off set
DIV($(vDateField)-CAL_WEEK_START,7)+1,
WEEK($(vDateField))) AS CAL_WEEK;
LOAD *,
DATE(YEARSTART($(vDateField))-WEEKDAY(YEARSTART($(vDateField)))-$(vFinWeekOS)) AS CAL_WEEK_START;
LOAD
DATE(TempDate) AS $(vDateField),
NUM(TempDate) AS $(vDateField)_NUM,
/**Script to make needed dates based on the Calendar Year**/
YEAR(TempDate) AS CAL_YEAR,
CAPITALIZE(REPLACE(MONTH(TempDate),'.',''))&'-'&YEAR((TempDate)) AS CAL_MONTH_YEAR,
MONTH(TempDate) AS CAL_MONTH,
NUM(MONTH(TempDate)) AS CAL_MONTH_NO,
WEEKDAY(TempDate) AS CAL_WEEKDAY,
DAY(TempDate) AS CAL_DAY,
DUAL('Q' & CEIL(MONTH(TempDate)/3), CEIL(MONTH(TempDate)/3)) AS CAL_QUARTER,
'Q' & CEIL(MONTH(TempDate)/3) AS CAL_QUARTER_DESC,
CEIL(MONTH(TempDate) /3) AS CAL_QUARTER_NO,

/**Script to make flags for use in set analysis**/

IF(YEAR(TempDate)=YEAR(TODAY(1)),1) AS F_CAL_CURRENT_YEAR,
IF(CEIL(MONTH(TempDate)/3) = CEIL(MONTH(TODAY(1))/3) AND YEAR(TempDate) = YEAR(TODAY(1)), 1) AS F_CAL_CURRENT_QUARTER,
IF(CEIL(MONTH(TempDate)/3) = CEIL(MONTH(TODAY(1))/3) AND YEAR(TempDate) = YEAR(TODAY(1)) AND DATE(TempDate) >= TODAY(1),1) AS F_CAL_QTD,
IF(MONTH(TempDate)= MONTH(TODAY(1)) AND YEAR(TempDate)=YEAR(TODAY(1)),1) AS F_CURRENT_MONTH,
IF(WEEK(TempDate)= WEEK(TODAY(1)) AND YEAR(TempDate)=YEAR(TODAY(1)),1) AS F_CURRENT_WEEK,
IF(TempDate = TODAY(1)-1, 1) AS F_YESTERDAY_FLAG,
IF(YEAR(TempDate)=YEAR(TODAY(1))-1,1) AS F_CAL_PRIOR_YEAR,
IF(CEIL(MONTH(TempDate)/3) = CEIL(MONTH(ADDMONTHS(TODAY(1),-3))/3) AND YEAR(TempDate) = YEAR(TODAY(1)), 1) AS F_PRIOR_QUARTER,
IF(MONTH(TempDate)= MONTH(ADDMONTHS(TODAY(1),-1)) AND YEAR(TempDate)=YEAR(ADDYEARS(TODAY(1),-1)),1) AS F_PRIOR_MONTH,
IF(WEEK(TempDate)= WEEK(TODAY(1)-7) AND YEAR(TempDate)=YEAR(ADDYEARS(TODAY(1),-1)),1) AS F_PRIOR_WEEK,
IF(YEAR(TempDate)= YEAR(TODAY(1)) AND TempDate <= TODAY(1),1) AS F_CAL_YTD,
IF(YEAR(TempDate)= YEAR(TODAY(1)) AND MONTH(TempDate)= MONTH(TODAY(1)) AND TempDate <= TODAY(1),1) AS F_MTD,
IF(CEIL(MONTH(TempDate)/3) = CEIL(MONTH(TODAY(1))/3) AND YEAR(TempDate) = YEAR(ADDYEARS(TODAY(1),-1)), 1) AS F_CAL_QUATER_LY,
IF(CEIL(MONTH(TempDate)/3) = CEIL(MONTH(TODAY(1))/3) AND YEAR(TempDate) = YEAR(ADDYEARS(TODAY(1),-1)) AND DATE(TempDate) >= ADDYEARS(TODAY(1),-1),1) AS F_CAL_QTD_LY,
IF(MONTH(TempDate) = MONTH(ADDYEARS(TODAY(1),-1)),1) AS F_MONTH_LY,
IF(TempDate = ADDYEARS(TODAY(1),-1),1) AS F_DAY_LY,
IF(YEAR(TempDate)= YEAR(ADDYEARS(TODAY(1),-1)) AND TempDate <= ADDYEARS(TODAY(1),-1),1) AS F_CAL_LYD,
IF(YEAR(TempDate)= YEAR(ADDYEARS(TODAY(1),-1)) AND MONTH(TempDate)= MONTH(ADDYEARS(TODAY(1),-1)) AND TempDate <= ADDYEARS(TODAY(1),-1),1) AS F_LYMD,
IF(TempDate <= TODAY(1) AND TempDate >= (TODAY(1)-7),1) AS F_ROLLING_7,
IF(TempDate <= TODAY(1) AND TempDate >= (TODAY(1)-30),1) AS F_ROLLING_30,
IF(TempDate <= TODAY(1) AND TempDate >= (TODAY(1)-60),1) AS F_ROLLING_60,
IF(TempDate <= TODAY(1) AND TempDate >= (TODAY(1)-90),1) AS F_ROLLING_90,
IF(TempDate <= TODAY(1) AND TempDate >= (TODAY(1)-180),1) AS F_ROLLING_180,
IF(TempDate <= TODAY(1) AND TempDate >= (TODAY(1)-365),1) AS F_ROLLING_365,
IF(TempDate <= MONTHSTART(TODAY(1)) AND TempDate >= ADDMONTHS(MONTHSTART(TODAY(1)),-12),1) AS F_ROLLING_12_MONTHS

Resident TempCalendar
Order By TempDate ASC;

ENDSUB

//Creates the Sub Routine to create a the Financial calendar
SUB Financial

FinancialCalendar:
LOAD *,
IF($(vWeekOS) =1, //If there Week start off set
DIV($(vDateField)-FIN_WEEK_START,7)+1,
WEEK(ADDMONTHS($(vDateField),$(vFinYearOS)))) AS FIN_WEEK;
LOAD *,
APPLYMAP('FinWeekMap',FIN_YEAR_START) AS FIN_WEEK_START;
LOAD
DATE(TempDate) AS $(vDateField),
NUM(TempDate) AS $(vDateField)_NUM,
/**Script to make needed dates based on the Fiscal Year**/

YEAR(ADDMONTHS(TempDate,$(vFinYearOS)))&'/'&YEAR(ADDYEARS(ADDMONTHS(TempDate,$(vFinYearOS)),1)) AS FIN_YEAR,
YEAR(ADDMONTHS(TempDate,$(vFinYearOS))) AS FIN_YEAR_START,
NUM(MONTH(ADDMONTHS(TempDate,$(vFinYearOS))))&'-'&YEAR((ADDMONTHS(TempDate,$(vFinYearOS)))) AS FIN_MONTH_YEAR,
NUM(MONTH(ADDMONTHS(TempDate,$(vFinYearOS)))) AS FIN_MONTH_NO,
DAY(ADDMONTHS(TempDate,$(vFinYearOS))) AS FIN_DAY,
DUAL('Q' & CEIL(MONTH(ADDMONTHS(TempDate,$(vFinYearOS)))/3), CEIL(MONTH(ADDMONTHS(TempDate,$(vFinYearOS)))/3)) AS FIN_QUARTER,
'Q' & CEIL(MONTH(ADDMONTHS(TempDate,$(vFinYearOS)))/3) AS FIN_QUARTER_DESC,
CEIL(MONTH(ADDMONTHS(TempDate,$(vFinYearOS)))/3) AS FIN_QUARTER_NO,

/**Script to make flags for use in set analysis**/

IF(YEAR(ADDMONTHS(TempDate,$(vFinYearOS)))= YEAR(ADDMONTHS(TODAY(1),$(vFinYearOS))),1) AS F_FIN_CURRENT_YEAR,
IF(YEAR(ADDMONTHS(TempDate,$(vFinYearOS)))=YEAR(ADDMONTHS(TODAY(1),$(vFinYearOS)))-1,1) AS F_FIN_PRIOR_YEAR,
IF(YEAR(ADDMONTHS(TempDate,$(vFinYearOS)))= YEAR(TODAY(1)) AND TempDate <= TODAY(1),1) AS F_FIN_YTD,
IF(YEAR(ADDMONTHS(TempDate,$(vFinYearOS)))= YEAR(ADDMONTHS(TODAY(1),$(vFinYearOS))) AND TempDate <= ADDYEARS(TODAY(1),-1),1) AS F_FIN_LYD,

IF(CEIL(MONTH(ADDMONTHS(TempDate,$(vFinYearOS)))/3) = CEIL(MONTH(ADDMONTHS(TODAY(1),$(vFinYearOS)))/3)
AND YEAR(ADDMONTHS(TempDate,$(vFinYearOS))) = YEAR(ADDMONTHS(TODAY(1),$(vFinYearOS))), 1) AS F_FIN_CURRENT_QUARTER,
IF(CEIL(MONTH(ADDMONTHS(TempDate,$(vFinYearOS)))/3) = CEIL(MONTH(ADDMONTHS(TODAY(1),$(vFinYearOS)))/3)
AND YEAR(ADDMONTHS(TempDate,$(vFinYearOS))) = YEAR(ADDMONTHS(TODAY(1),$(vFinYearOS)))
AND DATE(ADDMONTHS(TempDate,$(vFinYearOS))) >= ADDMONTHS(TODAY(1),$(vFinYearOS)),1) AS F_FIN_QTD,
IF(CEIL(MONTH(ADDMONTHS(TempDate,$(vFinYearOS)))/3) = CEIL(MONTH(ADDMONTHS(ADDMONTHS(TODAY(1),$(vFinYearOS)),-3))/3)
AND YEAR(ADDMONTHS(TempDate,$(vFinYearOS))) = YEAR(ADDMONTHS(TODAY(1),$(vFinYearOS))), 1) AS F_FIN_PRIOR_QUARTER,
IF(CEIL(MONTH(ADDMONTHS(TempDate,$(vFinYearOS)))/3) = CEIL(MONTH(ADDMONTHS(TODAY(1),$(vFinYearOS)))/3)
AND YEAR(ADDMONTHS(TempDate,$(vFinYearOS))) = YEAR(ADDYEARS(ADDMONTHS(TODAY(1),$(vFinYearOS)),-1)), 1) AS F_FIN_QUATER_LY,
IF(CEIL(MONTH(ADDMONTHS(TempDate,$(vFinYearOS)))/3) = CEIL(MONTH(ADDMONTHS(TODAY(1),$(vFinYearOS)))/3)
AND YEAR(ADDMONTHS(TempDate,$(vFinYearOS))) = YEAR(ADDYEARS(ADDMONTHS(TODAY(1),$(vFinYearOS)),-1))
AND DATE(ADDMONTHS(TempDate,$(vFinYearOS))) >= ADDYEARS(ADDMONTHS(TODAY(1),$(vFinYearOS)),-1),1) AS F_FIN_QTD_LY
Resident TempCalendar
Order By TempDate ASC;

ENDSUB

IF $(vFinancial) = 0 THEN //If financial calendar is is off then just build a normal calendar
CALL Calendar;

ELSE //Created both normal calendar and financial

CALL Calendar;
CALL Financial;

NOCONCATENATE
Calendar:
LOAD
*
RESIDENT MasterCalendar;

LEFT JOIN (Calendar)

LOAD
*
RESIDENT FinancialCalendar;

DROP TABLES MasterCalendar, FinancialCalendar;

ENDIF;

DROP TABLE TempCalendar;

EXIT SCRIPT;

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

A common use of subroutines is when you are repeating a block of code.  Variations in the code can be handled with parameters. For example, here is a Sub and Call statements to extract SQL tables and store to QVD.  On each call the value of the tablename (e.g. "Product") is placed in the variable "_table".

// Extract Subroutine. Parameter is SQL tablename.
SUB Extract (_table)
  [$(_table)]:
  SQL SELECT *
  FROM $(_table);
  STORE [$(_table)] INTO [$(_table)_Extract.qvd] (qvd);

  DROP TABLE [$(_table)];

ENDSUB // End of Subroutine

CALL Extract ('Product')
CALL Extract ('SalesOrderHeader')
CALL Extract ('SalesOrderDetail') 

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

its_rajvir
Creator
Creator
Author

Thankyou so much @Mark_Little 😊

its_rajvir
Creator
Creator
Author

Thankyou So much @rwunderlich  😊