Qlik Sense: How to use WHERE EXISTS to load QVDs with row based optimized?

[s_cantoni]

Hello,

I have a question about the condition "WHERE EXISTS" in the Qlik Sense data load editor.

From a list of QVDs I have to create n split tables for a dimension called "portfolio":

• step 1: For loop on QVDs list
• step 2: For loop on  "portfolio" values,
• step 3:  "where exists"  condition to filter "portfolio"  using QVD (row-based) optimized
• step 4: concatenate table with the same portfolio

Attached, the complete script.

My problem is that I would like to use row-based optimized to load QVDs with large size, but row-based optimized in "where exists" condition, only works if I use the same name of the field that I have to compare.

Using the same field name creates a problem when concatenating tables that are all resident and all have the same field.

For example,

I create the table "VALUE" with the column "PORTFOLIO", value 'R_STD12C' and row-based optimized works

NoConcatenate
VALUE:
LOAD PORTFOLIO INLINE
[PORTFOLIO
'R_STD12C'];

NoConcatenate
[FACT_TABLE_R_STD12C]:
LOAD
*
FROM '$(_QVDs)\AW_I17_000020_000004_38.qvd' (QVD)
WHERE EXISTS(PORTFOLIO,PORTFOLIO);

Concatenate([FACT_TABLE_R_STD12C])
LOAD
*
FROM '$(_QVDs)\AW_I17_000020_000007_43.qvd' (QVD)
WHERE EXISTS(PORTFOLIO,PORTFOLIO);

;

Instead the row based optimized does not work with the following script:

NoConcatenate
VALUE:
LOAD VALUE INLINE
[VALUE
'R_STD12C'];

NoConcatenate
[FACT_TABLE_R_STD12C]:
LOAD
*
FROM '$(_QVDs)\AW_I17_000020_000004_38.qvd' (QVD)
WHERE EXISTS(VALUE,PORTFOLIO);

Concatenate([FACT_TABLE_R_STD12C])
LOAD
*
FROM '$(_QVDs)\AW_I17_000020_000007_43.qvd' (QVD)
WHERE EXISTS(VALUE,PORTFOLIO);

;

Would you have any suggestions on how to solve this problem or how to set the algorithm in a different way?

Thank you

[rubenmarin]

Hi the optimized load only works when exists queries the same field, if you change the inline to:

VALUE:
LOAD VALUE as PORTFOLIO INLINE
[VALUE
'R_STD12C'];

So you can use exists as:

WHERE EXISTS(PORTFOLIO);

[s_cantoni]

Thanks for the quick reply.

This way it works, but my problem is that I have to do a loop on all the QVDs and all the portfolios to create different table for each portfolio. The portfolio field is used in many resident tables and when I use the "where" condition, extracts the wrong values from another table.

What you see in the text is just a piece of the code,  you can see the entire code in the attachment.

Would you have an idea of how I could handle it?

[rubenmarin · Accepted solution]

Well, to make it optimized you need to use the same name stored in qvd, so the only options I see is to store in qvd and drop the table and the end of each bucle, so the new table don't mess with values loaded in previous table.

Or rename the loaded fields to something like PORFOLIO_LOADED and at the end reanme it again with the correct name.