topic SQL not Exists in Qlikview in QlikView

SQL not Exists in Qlikview

Fri, 08 Jan 2016 17:12:43 GMT

Does anybody knows how can I create this in qlikview script?

SELECT

a.NR_ATENDIMENTO,

a.DT_ENTRADA_UNIDADE,

a.DT_SAIDA_UNIDADE,

a.CD_SETOR_ATENDIMENTO

FROM ATEND_PACIENTE_UNIDADE a

Where NR_ATENDIMENTO NOT IN (710590,665812,664556,145898)

and not exists (select 1

FROM ATEND_PACIENTE_UNIDADE x

Where x.NR_ATENDIMENTO = a. NR_ATENDIMENTO

And x.CD_SETOR_ATENDIMENTO not in (59,60) )

Re: SQL not Exists in Qlikview

swuehl — Fri, 08 Jan 2016 17:20:16 GMT

Have you tried just using above SQL SELECT statement in your LOAD script?

Do you get any error?

Re: SQL not Exists in Qlikview

Anonymous — Fri, 08 Jan 2016 17:24:01 GMT

I would create a view in SQL and then load the data from the view. Here is an example of how we populate a QVD directly from a view. We do this with all of our load scripts that have intense where statements like your example.

LOAD

Text(ClmNum) as ClmNum
, Text(ClmWrkShtNum) as ClmWrkShtNum
, Text(CoCd) as CoCd
, inpatient_days;

SQL
select
ClmNum
, ClmWrkShtNum
, CoCd
, "inpatient_days"

from
QlikViewStaging.dbo."v_claim_sum_inpatient_KBA"
where
inpatient_days > 0;

STORE InpatientDays into $(DataPath)InpatientDays.qvd;

Re: SQL not Exists in Qlikview

Fri, 08 Jan 2016 17:42:26 GMT

I didnt get any problem, just want to know how to reproduce specificaly the syntax "not exists" in qlikview since I have the raw data from ATEND_PACIENTE_UNIDADE.

Re: SQL not Exists in Qlikview

Fri, 08 Jan 2016 17:48:29 GMT

Actualy it's not intense... is very simple, but I dont know how to do this in qlikview script... create a view just for this not worth it.

Re: SQL not Exists in Qlikview

Fri, 08 Jan 2016 18:17:10 GMT

ok, I've found the sollution... thank you all!

SETOR_DEST_PAC_PA_INTERNADO_AUX_1:

LOAD

%NR_ATENDIMENTO,

CD_SETOR_REF_ATEND

From MOVIMENTO_SETOR.QVD(QVD);

and not Match(CD_SETOR_REF_ATEND,59,60);

//======================================================

SETOR_DEST_PAC_PA_INTERNADO_AUX_2:

LOAD

%NR_ATENDIMENTO,

DT_ADMISSAO_SETOR,

CD_SETOR_REF_ATEND

From MOVIMENTO_SETOR.QVD(QVD)

where not Exists (%NR_ATENDIMENTO);

;

drop Table SETOR_DEST_PAC_PA_INTERNADO_AUX_1;