How to use sub routines in Qlik Sense®

At times, it is mandatory to use the same set of code at different places in the script. To achieve this, developers will sometimes use a copy and paste approach. However, this makes it difficult to maintain and read the script. It is always advised to create subroutines and then simply call them as and when needed.

In the following recipe, we use subroutines to create QVDs and store them in a specific folder. We also generate fields using various functions within the subroutines, which also helps in auditing the QVD files.

Getting ready

  1. This recipe makes use of certain functions such as QVDTablename, QVDNoOfFields and QVDNoOfRecords, which don't work in normal script mode in Qlik Sense. Hence, we need to activate the legacy mode by following the steps given in the recipe titled How to activate Legacy mode in Qlik sense in Chapter 1, Getting Started with the Data.
  2. Once the legacy mode is activated, open Qlik Sense desktop and create a new application called Subroutines in Qlik Sense.
  3. Create a folder called QVD at a desired location on the hard drive. For the sake of this recipe, we are creating the QVD folder at the following location:
    C:Qliksense cookbookChapters3QVD
  4. This folder will store the QVDs generated in the subroutines.

How to do it…

  1. Open the data load editor.
  2. Create a new data connection called as QVDFolder. This data connection should create a folder connection to the QVD folder created in step 1.
  3. In the data load editor, create a new section Variable Setting, and add the following code to it:
    LET vFileName = subfield(DocumentName(),'.',1);

SET vTable1 =1; //Product

SET vTable2 =1; //Customer

LET vQVD='C:Qliksense cookbookChapters3';
  4. Create a new Section Data, and add the following code to it:
    SUB Create_T_Product

  $(vTable):
  LOAD * INLINE [ 
  Product, ProductID, Sales 
  Footwear, F21Lon, 120000
  Tyres, T21Man, 150000 
  Mountain Bikes, MB32Lon, 195000 
  Road Bikes, RB12Bir, 225000 
];
END SUB

SUB Create_T_Customer

  $(vTable):
  LOAD * INLINE [ 
  Customer, ProductID, City 
  Hero, F21Lon, London
  Avon, T21Man, Manchester 
  Force1, MB32Lon, London 
  Ferrari, RB12Bir, Birmingham 
];
END SUB
  5. Create a new section called Store_Drop and add the following code to it:
    SUB Create_QVD_Standard(vTable,vSub)

  LET vQVDStartTime = num(now());
  CALL $(vSub)

  STORE '$(vTable)' INTO $(vQVD)QVD$(vTable).qvd(qvd);

  DROP TABLE $(vTable);

  LET vFieldType = 'QVD_Standard';

  LET vQVDEndTime = num(now());

  LET vQVDTimeTaken = $(vQVDEndTime) - $(vQVDStartTime);

  LET vTableFullPath = DocumentPath();

  TablesLoaded:
  LOAD
  QVDTableName('$(vQVD)QVD$(vTable).qvd') AS [STDQVD Name],
  Timestamp($(vQVDStartTime),'DD MMM YY hh:mm') AS [STDQVD Start Time],
  Timestamp($(vQVDEndTime),'DD MMM YY hh:mm') AS [STDQVD End Time],
  Interval($(vQVDTimeTaken),'hh mm ss') AS [STDQVD Time Taken (hh mm ss)],
  QVDNoOfFields('$(vQVD)QVD$(vTable).qvd') AS [STDQVD No of Fields],
  QVDNoOfRecords('$(vQVD)QVD$(vTable).qvd')AS [STDQVD No of Records]
  AUTOGENERATE (1);

END SUB
  6. Create a new section called Create qvd and add the following code to it:
    LET vRunStart = timestamp(now(),'DD MMM YYYY hh:mm:ss');

If $(vTable1) = 1 Then
CALL Create_QVD_Standard('T_Product','Create_T_Product')
ENDIF;

If $(vTable2) = 1 Then
CALL Create_QVD_Standard('T_Customer','Create_T_Customer')
ENDIF;

LET vRunFinish = timestamp(now(),'DD MMM YYYY hh:mm:ss');

LET vRunTime = Interval(num(timestamp#('$(vRunFinish)','DD MMM YY hh:mm:ss'))
-num(timestamp#('$(vRunStart)','DD MMM YY hh:mm:ss')),'hh:mm:ss');
  7. Save and reload the document.
  8. On the front end, click on edit at the top right hand corner and create a new Table object by dragging it across the sheet from the left hand side panel.
  9. Add all the available dimensions in the table to get the following output:
    How to do it…

How it works…

The first two subroutines named SUB Create_T_Product and SUB Create_T_Customer create the tables called Product and Customer and then store the data in these tables.

The third subroutine SUB Create_QVD_Standard(vTable,vSub) passes the values of the respective table names and the subroutines. Within this sub routine we also create a number of fields using the load script functions, which are used for our QVD audit purposes.

Further, the CALL statements call the subroutines and create QVDs to store them in specified folders.

Along with creating and storing the QVDs, we also get valuable information, such as the number of fields in each QVD, the time it takes to create the QVDs, and so on. It is especially helpful while loading a large dataset.

There's more…

The subroutines can be stored in an external file and further used in the script using an include statement.

See also

  • Packaging the code in script files
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset