topic Subselect statement resident in QlikView

Subselect statement resident

francisvandergr — Mon, 06 Jun 2011 11:10:06 GMT

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)

Subselect statement resident

martinpohl — Mon, 06 Jun 2011 11:20:47 GMT

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

Subselect statement resident

francisvandergr — Mon, 06 Jun 2011 14:43:36 GMT

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.

Subselect statement resident

martinpohl — Mon, 06 Jun 2011 14:49:05 GMT

so change to:

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

Subselect statement resident

francisvandergr — Mon, 06 Jun 2011 15:01:38 GMT

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

Re: Subselect statement resident

johnw — Tue, 07 Jun 2011 00:46:28 GMT

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.