Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Exclude data from load

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

1 Solution

Accepted Solutions

Re: Exclude data from load

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

10 Replies

Re: Exclude data from load

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

jsn
Honored Contributor

Re: Exclude data from load

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

Not applicable

Re: Exclude data from load

Hi,

try

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

Not applicable

Re: Exclude data from load

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

the where condition must be as follows

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

Not applicable

Re: Exclude data from load

Hello,

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

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

Greets

Klaus

anantmaxx
Valued Contributor

Re: Exclude data from load

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
Contributor

Re: Exclude data from load

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

Not applicable

Re: Exclude data from load

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