Subselect statement resident

[francisvandergr]

I have a question. I wanna load records from a SQL database which are not in a resident table. Is this possible ?

I thougt something like:

select

field_ a,

field_b,

from SQLTABLE where field_a not in (resident QVTABLE) 

[martinpohl]

try:

where not exists (field_a)

unfortunately you can not define in table, the value field_a is checked for all datas.

If you need only the values from table1 load the value field_1 additional as field_exist and change the syntax to

where not exists( field_exist, field_a)

Regards

[francisvandergr]

Hi Martin,

Thank you for your answer. Maybe i do not understand you enough. Now i include a part of my script.

Orderfacturen:
NULLASVALUE *;
load
Factuurnummer,
Crediteur,
Cred\Openstaand,
Ordernummer,
artcode,
Ontvangenbedrag

Resident Crediteuren_tmp where Ontvangenbedrag > 0 or Ontvangenbedrag < 0;
drop table Crediteuren_tmp;

concatenate
NULLASVALUE *;
load
Factuurnummer,
Crediteur,
Cred\Openstaand,
Ordernummer,
artcode,
Verschilbedrag as Ontvangenbedrag
Resident Crediteuren_tmp2 where Verschilbedrag <> 0;
drop table Crediteuren_tmp2;

  What i want with the part after concatenate is: only load  records with the same FACTUURNUMMERS which are in

the table orderfacturen.

[martinpohl]

so change to:

Resident Crediteuren_tmp2 where Verschilbedrag <> 0 and not exists (Factuurnummer);

[francisvandergr]

I have chaged it to

Resident Crediteuren_tmp2 where Verschilbedrag <> 0 and exists (Factuurnummer);

Because i want concatenate records with Fcatuurnummer which are also in Orderfacturen. But this doesn't work. Sorry. I see all invoices

[johnw]

francisvandergrijn wrote:

I have a question. I wanna load records from a SQL database which are not in a resident table. Is this possible ?

I thougt something like:

select

field_ a,

field_b,

from SQLTABLE where field_a not in (resident QVTABLE)

Something like this if it's important for performance to do it on the SQL side instead of the QlikView side:

[field_a values]:
LOAD concat(field_a,chr(39)&','&chr(39)) as "field_a values"
;
LOAD DISTINCT field_a // there's a faster way to do this if field_a is ONLY in QVTABLE at this point
RESIDENT QVTABLE
;
LET vFieldAValues = peek('field_a values');
DROP TABLE [field_a values];

SomeTable:
LOAD *
;
SQL SELECT
field_a
,field_b
FROM SQLTABLE
WHERE field_a NOT IN('$(vFieldAValues)')
;

Hmmm, and then it looks like you're asking for something completely different in your next post.  So I'm probably misunderstanding.  Posting it anyway just in case.