Qlik Community

Connectivity & Data Prep

Discussion board where members can learn more about Qlik Sense Data Connectivity.

Announcements
Coming Aug. 9: New Simplified Authoring for Qlik Sense SaaS – For Details, CLICK HERE
cancel
Showing results for 
Search instead for 
Did you mean: 
s_cantoni
Partner - Contributor
Partner - Contributor

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

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

Labels (1)
1 Solution

Accepted Solutions
rubenmarin

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.

View solution in original post

3 Replies
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
Partner - Contributor
Partner - Contributor
Author

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

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.