topic Re: Nested LOAD Statment in QlikView

Nested LOAD Statment

matthewjbryant — Thu, 04 Jun 2015 11:59:34 GMT

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?

Re: Nested LOAD Statment

rubenmarin — Thu, 04 Jun 2015 12:18:45 GMT

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;

Re: Nested LOAD Statment

MayilVahanan — Thu, 04 Jun 2015 12:21:23 GMT

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

Re: Nested LOAD Statment

matthewjbryant — Thu, 04 Jun 2015 12:24:29 GMT

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

Re: Nested LOAD Statment

matthewjbryant — Thu, 04 Jun 2015 13:36:39 GMT

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?

Re: Nested LOAD Statment

rubenmarin — Thu, 04 Jun 2015 13:48:05 GMT

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);

...