Nested LOAD Statment

[matthewjbryant]

Is it possible to replicate a nested SELECT in Qlikview?

I have a situation where in PL/SQL I would write something like the following

SELECT

   CASE

      WHEN COLUMN1 NOT IN (SELECT COLUMN2 FROM TABLE2) THEN

      'Ok'

      ELSE 'Not Ok'

   END

FROM TABLE1;

Is it possible to do a similar thing with the LOAD statement in Qlikview?

[rubenmarin · Accepted solution]

Hi Matthew, one way to do that is loading the values of table 2 and check using Exists(), ie:

Table2Column2:

LOAD Distinct COLUMN2 as CheckColumn2 FROM Table2;

Table1:

LOAD If(Exists('CheckColumn2', COLUMN1), 'Ok', 'Not OK') as Check

FROM TABLE1;

DROP Table Table2Column2;

[MayilVahanan]

Hi

If the script is more complicated, then You can load the sql statement directly to qlikview.

[matthewjbryant]

I didn't mention that the SQL was just an example; sorry. I'm dealing with 2 QVDs instead of database tables.

[matthewjbryant]

Thanks. This worked perfectly. I'd seen the EXISTS() function used in other ways, but couldn't find this.

One question: what if you had 2 QVWs with the same column name, but only want to check if a new column exists in one of them? Do you create pseudo-columns both of the QVWs?

[rubenmarin]

I'm guessing each qvw has those columns names previoulsy loaded in a qvd (if not, you can create one while reloading), I think the easiest way is loading both qvds in the field CheckColumn2:

Table2Column2:

LOAD Distinct COLUMN2 as CheckColumn2 FROM qvw1.Table2.qvd(qvd);

Concatenate LOAD Distinct COLUMN2 as CheckColumn2 FROM qvw2.Table2.qvd(qvd);

...