topic Re: Exclude data from load in QlikView

Exclude data from load

Tue, 19 Mar 2013 21:08:46 GMT

I cannot figure out how to exclude names containing 'coach'.
Demographic:

LOAD

1 as Counter,

LEGAL_NAME
FROM Census.txt

(txt, codepage is 1252, embedded labels, delimiter is ',', msq)

WHERE LEGAL_NAME <> '*coach*';

Re: Exclude data from load

kaushiknsolanki — Wed, 20 Mar 2013 06:26:24 GMT

Hi,

Try this.

Load

1 as Counter,

LEGAL_NAME

FROM Census.txt

(txt, codepage is 1252, embedded labels, delimiter is ',', msq)

WHERE not wildmatch(LEGAL_NAME,'*coach*');

Regards,

Kaushik Solanki

Re: Exclude data from load

Anonymous — Wed, 20 Mar 2013 07:25:49 GMT

Try this:

WHERE INDEX(LEGAL_NAME, 'coach')=0;

(if you're dealing with a massive data set at some point it might be better to load all the "Coach" values first and do a left keep to eliminate them from the full table load - for performance reasons. )

Re: Exclude data from load

Wed, 20 Mar 2013 07:37:44 GMT

Hi,

try

WHERE Not WildMatch(LEGAL_NAME, '*coach*');

Re: Exclude data from load

Wed, 20 Mar 2013 07:58:02 GMT

try this

Demographic: LOAD 1 as Counter, LEGAL_NAME FROM Census.txt (txt, codepage is 1252, embedded labels, delimiter is ',', msq) WHERE LEGAL_NAME <> pick(match(LEGAL_NAME,'coach'),'coach');

Re: Exclude data from load

alexandros17 — Wed, 20 Mar 2013 08:04:43 GMT

the where condition must be as follows

WHERE index(LEGAL_NAME,'coach') =0;

Re: Exclude data from load

Wed, 20 Mar 2013 08:19:07 GMT

Hello,

use where not wildmatch(LEGAL_NAME, '*coach*')

because wildcards are not recognized by the operators = and <>

Greets

Klaus

Re: Exclude data from load

Anonymous — Wed, 20 Mar 2013 08:23:40 GMT

Hello,

1. if coach exist at left of names WHERE left(LEGAL_NAME,5) <> 'coach'; 2. if coach exist at right of names WHERE right(LEGAL_NAME,5) <> 'coach';

3. if coach exist anywhere in field name the wildmatch(LEGAL_NAME, '*coach*') <> coach;

Regards,

Anant

Re: Exclude data from load

michael123 — Wed, 20 Mar 2013 08:30:19 GMT

Demographic:

LOAD

1 as Counter,

LEGAL_NAME

FROM Census.txt

(txt, codepage is 1252, embedded labels, delimiter is ',', msq)

WHERE NOT WILDMATCH(LEGAL_NAME,'*coach*');

Re: Exclude data from load

Wed, 20 Mar 2013 14:00:52 GMT

when I answered, it seemed to be the first answer.

Now I know that I was the 6th.

Moderating the answers first, has an annoying sideeffect.

Greets

Klaus

Re: Exclude data from load

Wed, 20 Mar 2013 17:06:52 GMT

Thanks for all the help.