topic Re: Use external (xls) list as filter in data load editor in Connectivity & Data Prep

Use external (xls) list as filter in data load editor

Mon, 23 Jan 2017 16:11:03 GMT

Hi all,

I'm hoping someone can help me pointing into right direction.

I'm trying to load a list of values (field ambiti in tab_ambiti) and I want to use them as a filter in where clause in table conformita_new.

How can I do this without a join betwwen tables?

Thx

Marco

tab_ambiti:

LOAD

ambiti

FROM [lib://AttachedFiles/ambiti.xls]

(biff, embedded labels, table is Sheet1$);

Qualify *;

Unqualify key_1, COD_LOC_CONTROLLO;

conformita_new:

LOAD

COD_LOC_CONTROLLO&SUBJECT_AREA&DT_ESEC_CONTROLLO as key_1,

COD_LOC_CONTROLLO,

COD_LOC_AZIENDA,

DT_RIF_CONTROLLO,

RUN,

DT_ESEC_CONTROLLO,

"MONTH",

DT_ESEC_CONTROLLO_MM,

%KO,

CRITERIO_ITA,

SUBJECT_AREA,

DT_AGGIORNAMENTO

FROM [lib://QVD (spimi_qlikadministrator)/esiti.qvd]

(qvd)

Re: Use external (xls) list as filter in data load editor

marcus_sommer — Mon, 23 Jan 2017 16:21:44 GMT

In this case you could just use:

...

where exists(ambiti, conformita_new);

as condition for your qvd-load.

- Marcus

Re: Use external (xls) list as filter in data load editor

Mon, 23 Jan 2017 17:08:06 GMT

Hi Marcus, thx for your answer.

I tried to use exists , but qlik show me the following error message:

Si è verificato il seguente errore:

Field not found - <conformita_new>

---

L'errore si è verificato qui:

conformita_new:

LOAD

COD_LOC_CONTROLLO&SUBJECT_AREA&DT_ESEC_CONTROLLO as key_1,

COD_LOC_CONTROLLO,

COD_LOC_AZIENDA,

DT_RIF_CONTROLLO,

RUN,

DT_ESEC_CONTROLLO,

"MONTH",

DT_ESEC_CONTROLLO_MM,

%KO,

CRITERIO_ITA,

SUBJECT_AREA,

DT_AGGIORNAMENTO

FROM [lib://QVD (spimi_qlikadministrator)/esiti.qvd]

(qvd)

where exists(ambiti, conformita_new)

I don't understand because conformita_new is read as a field.

Marco

Re: Use external (xls) list as filter in data load editor

marcus_sommer — Mon, 23 Jan 2017 17:34:48 GMT

Sorry I haven't read carefully enough and now I see that this is the tablename and not the filter-field - therefore try this:

...

where exists(ambiti, COD_LOC_CONTROLLO);

by assuming that COD_LOC_CONTROLLO is your filter-field because you didn't mention the fieldname.

- Marcus

Re: Use external (xls) list as filter in data load editor

Mon, 23 Jan 2017 17:52:52 GMT

Ok, now it works, I used another field, but it works. Thank you! Marco