On-Demand App Generation (ODAG) from QVDs

Have you ever wondered if you can perform ODAG on top of QVDs instead of going back to the database? If you have, then you are at the right place as we will see in this blog how this can be done using QVDs.

If you are looking at this post, it is safe to assume you already know what ODAG is. If not, we encourage you to start from the Qlik Sense help section: Managing big data with on-demand apps.

Now since we have that out of our way, here are three ways to do this:

1. Building a where match statement
2. Building a where exists statement
3. Constructing For Loop

1. Building a where match statement

The goal is to construct a where clause which can be used with a QVD. Qlik Sense help page gives a subroutine that builds a where clause that can be used with the SQL database.

SUB ExtendWhere(Name, ValVarName)
   LET T = Name & '_COLNAME';
   LET ColName = $(T);
   LET Values = $(ValVarName);
   IF len(Values) > 0 THEN
      IF len(WHERE_PART) > 0 THEN
         LET WHERE_PART = '$(WHERE_PART) AND $(ColName) IN ($(Values) )';
      ELSE
         LET WHERE_PART = ' WHERE $(ColName) IN ( $(Values) )';
      ENDIF
   ENDIF
END SUB;

SUB ExtendWhere(Name, ValVarName)
   LET T = Name & '_COLNAME';
   LET ColName = $(T);
   LET Values = $(ValVarName);
   IF len(Values) > 0 THEN
      IF len(WHERE_PART) > 0 THEN
         LET WHERE_PART = '$(WHERE_PART) and Match([$(ColName)], $(Values))';
      ELSE
         LET WHERE_PART = 'Where Match([$(ColName)], $(Values))';
      ENDIF
   ENDIF
END SUB;

TRACE $(WHERE_PART);
 

2. Building a where exists statement

In addition to the above, the Qlik Sense help page provides a way to create a binding using an Inline table. We can use this to create a table of selected value and use this as a where exists statement.

selected_region:
LOAD * INLINE [
   Region
   $(ods_Region){"quote": "", "delimiter": ""}
];

The above code will translate into (assuming that the user selected CA and NA region in the selection app)

selected_region:
LOAD * INLINE [
   Region
   CA
   NA
];

And now this can be used as Where Exists(Region); to optimally load data from QVDs.

3. Constructing For Loop

This method is particularly helpful if the data is stored into multiple QVDs and you would want to load from selected QVDs. For example, the data from each region is stored in its own qvd (Transactions_NA.qvd, Transactions_CA.qvd, and Transactions_SA.qvd) and you only want to load the data from the selected region. Using the same code as we used in section 2 we can build an inline-table

selected_region:
LOAD * INLINE [
   Region
   $(ods_Region){"quote": "", "delimiter": ""}
];

Next, we can construct a for loop statement like this to load from only selected regions

FOR i = 1 to FieldValueCount(‘Region’)
   
   LET vRegion = FieldValue(‘Region’, $(i));

   Transaction_Details:
   LOAD TransID,
        Region,
        "Date"
   FROM [Transactions_$(vRegion).qvd] (qvd)
   $(WHERE_PART);

NEXT

DROP Table selected_region;

Note: It is important to know that the above three methods can be used in combination with each other.

Let us help you take your Qlik skills to the next level!

About the Author:

Sunny is a Qlik MVP since 2016 and has been actively helping people with their quest to learn on the Qlik community. He is a quick learner and is continually looking for ways to expand his business acumen.

Note: ODAG only works on the server.