topic Re: Where Exists ID in QlikView

Where Exists ID

Mon, 15 Sep 2014 14:18:06 GMT

Hi,

I already read several discussions concerning "where exists", but unfortunately it doesn't seem to work for me (though I think it should be quite simple).

I got two tables (SQL) which both have the column id. I just want to load the rows of the second table which have an id which exists in table one.

Table1:

LOAD Id as ID,

Title;

SQL SELECT *

FROM datbase.table1;

Table2:

LOAD `task_id` as ID ,

`label_id`,

where exists(ID);

SQL SELECT *

FROM database.table2;

I hope anyone can help me Thanks in advance

Re: Where Exists ID

MK_QSL — Mon, 15 Sep 2014 14:21:48 GMT

Table2:

LOAD `task_id` as ID ,

`label_id`,

where exists([task_id],ID);

SQL SELECT *

FROM database.table2;

Re: Where Exists ID

JonnyPoole — Mon, 15 Sep 2014 14:24:22 GMT

Do you want to add the rows from Table2 to the bottom of Table1 ? if so then just add a 'concatenate' key word in lieu of the line 'Table2:'

if not and you want the tables separate its a bit more complicated and needs some joining , filtering and resident loads. But then it would associate to Table1 so i'm not sure you want that.

Re: Where Exists ID

giakoum — Mon, 15 Sep 2014 14:24:25 GMT

Hi Corinna.

if ID is not unique per record, QV only loads the 1st row it encounters. Could that be the case?

Re: Where Exists ID

Mon, 15 Sep 2014 16:43:17 GMT

you can write

where task_id not in()

Re: Where Exists ID

martynlloyd — Mon, 15 Sep 2014 16:58:21 GMT

Hi,

The most common use of exists is Where NOT Exists...

You would be better to use a Left Join for what you are doing.

Regards,

Re: Where Exists ID

maxgro — Mon, 15 Sep 2014 22:22:26 GMT

Table2:

LOAD task_id as ID ,

label_id,

where exists(ID, task_id);

SQL SELECT *

FROM database.table2;

Re: Where Exists ID

michael_doetter — Fri, 19 Sep 2014 09:38:13 GMT

Hallo Corinna,

ich hatte mal ein ähnliches Problem. Habe es einfach über LEFT JOIN ID gelöst.

Tabelle 1 alle Daten / Tabelle 2 nur die Daten wo in Tabelle 1 die ID vorhanden ist.

Klappt prima. Vielleicht hilft es Dir.

Micha