Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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;
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;
Hi
If the script is more complicated, then You can load the sql statement directly to qlikview.
I didn't mention that the SQL was just an example; sorry. I'm dealing with 2 QVDs instead of database tables.
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?
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);
...