Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

SQL not Exists in Qlikview

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) )

Tags (1)
1 Solution

Accepted Solutions
Not applicable

Re: SQL not Exists in Qlikview

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;

5 Replies
MVP
MVP

Re: SQL not Exists in Qlikview

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

Do you get any error?

lubicz99
Valued Contributor

Re: SQL not Exists in Qlikview

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;

Not applicable

Re: SQL not Exists in Qlikview

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.

Not applicable

Re: SQL not Exists in Qlikview

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.

Not applicable

Re: SQL not Exists in Qlikview

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;

Community Browser