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:
- Building a where match statement
- Building a where exists statement
- 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.
Where Statement for 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;
We can modify this subroutine to construct a where clause for loading data from a QVD.
Where Statement for QVDs
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;
Hint: Add a TRACE statement in the script before using it within the where statement to see how it is defined
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:

Note: ODAG only works on the server.
Great post Sunny! I haven’t seen this approach outlined before.
Great article , again learned a new thing today 🙂