Exclude data from load

[#1]

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*';

[kaushiknsolanki · Accepted solution]

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

[Anonymous]

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

[#4]

Hi,

try

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

[#5]

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

[alexandros17]

the where condition must be as follows

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

[#7]

Hello,

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

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

Greets

Klaus

[Anonymous]

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

[michael123]

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*');

[#10]

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