topic Re: "where" statement in QlikView

"where" statement

Tue, 17 Jun 2014 12:06:52 GMT

Hi Everyone

I have the following script:

LOAD *,

mid(job_nummer, 11,2) as Gesellschaft;

I would like to include only values that are "AG", "AP" and "DC"

How would I have to script this?

Thank you very much for your help!

Re: "where" statement

ashfaq_haseeb — Tue, 17 Jun 2014 12:09:42 GMT

Hi try below

LOAD *,

mid(job_nummer, 11,2) as Gesellschaft;

FROM EASY.job where "datum_auftrag" is not null

and mid(job_nummer, 11,2) in ("AG", "AP" and "DC);

Regards

ASHFAQ

Re: "where" statement

Tue, 17 Jun 2014 12:13:36 GMT

hm, that one does not work for me...

Re: "where" statement

ashfaq_haseeb — Tue, 17 Jun 2014 12:18:28 GMT

Hi try this

LOAD *,

mid(job_nummer, 11,2) as Gesellschaft;

FROM EASY.job where "datum_auftrag" is not null

and mid(job_nummer, 11,2) in ('AG', 'AP' and 'DC');

Regards

ASHFAQ

Re: "where" statement

Tue, 17 Jun 2014 12:23:07 GMT

Thank you for your help Ashfaq. Still get an error with this code...

Re: "where" statement

tresB — Tue, 17 Jun 2014 12:23:54 GMT

LOAD *,

mid(job_nummer, 11,2) as Gesellschaft

resident <> Where Match(job_number, 'AG', 'AP' ,'DC');

Re: "where" statement

gandalfgray — Tue, 17 Jun 2014 12:24:10 GMT

The Load you are trying to do, is that from a sql source or from a resident table?

Re: "where" statement

tresB — Tue, 17 Jun 2014 12:25:58 GMT

And, if you are loading from DB then try like Ashfaq as suggested with a small correction: remove And and try like :

in ('AG', 'AP' , 'DC');

Re: "where" statement

gandalfgray — Tue, 17 Jun 2014 12:28:45 GMT

And if loading from resident I think it should be:

where match(mid(job_nummer, 11,2),'AG', 'AP', 'DC');

Re: "where" statement

Tue, 17 Jun 2014 12:41:35 GMT

Hi GandalfiGray

I am loading data from a pervasive database.

Thank you for your help...

Re: "where" statement

Tue, 17 Jun 2014 12:55:16 GMT

Hi treseco

I am not sure i got what you were trying to do. Could you write the whole script?

Thank you for your help!

Re: "where" statement

tresB — Tue, 17 Jun 2014 13:00:51 GMT

I guess, there is a understanding gap. Could you explain expected output against a sample input?

Re: "where" statement

Tue, 17 Jun 2014 13:12:12 GMT

Here is what I would like to do:

I have the following script:

LOAD *,

mid(job_nummer, 11,2) as Gesellschaft;

I would like to include only values that are "AG", "AP" and "DC"

The data I load from a pervasive database so the whole script looks like this:

LOAD *,

mid(job_nummer, 11,2) as Gesellschaft;

SQL SELECT "job_nummer",

jobnr,

"datum_auftrag",

Year ("datum_auftrag") as Jahr,

Month ("datum_auftrag") as Monat,

jobkategorie

FROM EASY.job where "datum_auftrag" is not null;

Re: "where" statement

gandalfgray — Tue, 17 Jun 2014 13:16:47 GMT

Try

LOAD *,

mid(job_nummer, 11,2) as Gesellschaft

where match(mid(job_nummer, 11,2),'AG', 'AP', 'DC');

SQL SELECT "job_nummer",

jobnr,

"datum_auftrag",

Year ("datum_auftrag") as Jahr,

Month ("datum_auftrag") as Monat,

jobkategorie

FROM EASY.job where "datum_auftrag" is not null;

Re: "where" statement

anbu1984 — Tue, 17 Jun 2014 13:18:33 GMT

LOAD *,

mid(job_nummer, 11,2) as Gesellschaft

Where Match(mid(job_nummer, 11,2), 'AG', 'AP' ,'DC');

SQL SELECT "job_nummer",

jobnr,

"datum_auftrag",

Year ("datum_auftrag") as Jahr,

Month ("datum_auftrag") as Monat,

jobkategorie

FROM EASY.job where "datum_auftrag" is not null;

Re: "where" statement

tresB — Tue, 17 Jun 2014 13:21:58 GMT

You can filter it at the DB level like:

LOAD *,

mid(job_nummer, 11,2) as Gesellschaft;

SQL SELECT "job_nummer",

jobnr,

"datum_auftrag",

Year ("datum_auftrag") as Jahr,

Month ("datum_auftrag") as Monat,

jobkategorie

FROM EASY.job where "datum_auftrag" is not null And "Job_number" in ('AG', 'AP' ,'DC');

Re: "where" statement

Tue, 17 Jun 2014 13:30:03 GMT

AWESOME! Thank you anbu

Re: "where" statement

gandalfgray — Tue, 17 Jun 2014 13:33:51 GMT

And what's wrong with my answer?

I think it is fair to mark the first correct answer as correct.

Re: "where" statement

Tue, 17 Jun 2014 14:07:35 GMT

Sorry GandalfGray. I do not get your answer to work. I will try again. I definately appechiate your help...

Re: "where" statement

Tue, 17 Jun 2014 14:08:47 GMT

You are right, you entered the same code. My apologies...