topic Exists in QlikView

Exists

upaliwije — Fri, 30 Sep 2016 10:41:15 GMT

I have following data load script with exits command but It does not execute with an error message. pls correct me

Let Vdate=Num(Date(Makedate(2015,09,01),'D/M/YYYY'));

debtors:

LOAD *,

DATE(FLOOR(TRAN_DAT))AS TRAN_DATE,

DATE(FLOOR(RISK_DAT))AS RISK_DATE

where num(date(TRAN_DAT,'D/M/YYYY'))>=$(Vdate);

sql

select

det_debt_code AS DEBTOR_CODE,

trunc(det_ref_date) as Tran_dat,

trunc(det_risk_date) as Risk_dat,

det_hc_amt as Amount,

det_ref_c as Ref_c,

det_ref_2 as Ref_no,

det_txntype as TXN_type

from debtor

where det_txntype='1'

;

load *

where Exists(Ref_c);

sql

select

det_debt_code AS DEBTOR_CODE,

trunc(det_ref_date) as Tran_dat,

trunc(det_risk_date) as Risk_dat,

det_hc_amt as Amount,

det_ref_c as Ref_c,

det_ref_2 as Ref_no,

det_txntype as TXN_type,

det_ref_c as Ref_c,

det_txntype as TXN_type

from debtor

where det_txntype<>'1'

;

I want to load data from 2nd script where Ref_c matches with Ref_c of 1st script

Re: Exists

giakoum — Fri, 30 Sep 2016 10:45:20 GMT

QlikView is translating this as the same load.

Just put a table name before the 2nd load :

Final:

load *

where Exists(Ref_c);

sql

select

det_debt_code AS DEBTOR_CODE,

trunc(det_ref_date) as Tran_dat,

trunc(det_risk_date) as Risk_dat,

det_hc_amt as Amount,

det_ref_c as Ref_c,

det_ref_2 as Ref_no,

det_txntype as TXN_type,

det_ref_c as Ref_c,

det_txntype as TXN_type

from debtor

where det_txntype<>'1'

;

and drop the 1st table when done.

is Ref_c unique?

Re: Exists

upaliwije — Fri, 30 Sep 2016 10:49:45 GMT

Still I get the error massge below

Field not found - <Ref_c>

Re: Exists

giakoum — Fri, 30 Sep 2016 10:52:29 GMT

load only the 1st table

and then check field names

Sometimes Oracle changes every field to uppercase so maybe the name is REF_C and not Ref_c

Re: Exists

zhadrakas — Fri, 30 Sep 2016 10:53:32 GMT

Try Upper case

where Exists(REF_C);

Re: Exists

upaliwije — Fri, 30 Sep 2016 10:59:35 GMT

You are right

Thanks