topic Re: Need only capitial text data from column in QlikView

Need only capitial text data from column

ajaykumar1 — Thu, 04 Jun 2015 11:54:27 GMT

Hi Everyone,

I have the data like below:

Table:

Load * inline

[

COMPANY

abc corp

ABC CORP

TCP inc

In tcp inc

Google Inc

];

Load *,

if(wildmatch(COMPANY,'ABC','TCP'),'YES','NO') AS DERIVED_COMPANY

Resident Table;

DROP Table Table;

Req: I need capture 'ABC','TCP' from the field COMPANY.That means only capital text.If its CAPS text then i need to show YES else NO

If i select YES in front end only ABC CORP and TCP inc should come.In NO case remaining all.

Plz correct my above formula.

More Thanks,

Ajay

Re: Need only capitial text data from column

swuehl — Thu, 04 Jun 2015 12:01:21 GMT

You need to add wild card *:

Load *,

if(wildmatch(COMPANY,'*ABC*','*TCP*'),'YES','NO') AS DERIVED_COMPANY

Resident Table;

edit: Ah, I haven't considered that wildmatch performs case insensitive matching.

Re: Need only capitial text data from column

sunny_talwar — Thu, 04 Jun 2015 12:01:47 GMT

Try this:

Table:

Load * inline

[

COMPANY

abc corp

ABC CORP

TCP inc

In tcp inc

Google Inc

];

Load *,

If(WildMatch(PurgeChar(COMPANY, 'abcdefghijklmnopqrstuvwxyz'),'*ABC*','*TCP*'),'YES','NO') as DERIVED_COMPANY

Resident Table;

DROP Table Table;

Output:

Re: Need only capitial text data from column

ajaykumar1 — Thu, 04 Jun 2015 12:06:14 GMT

Thanks Sun and Swehel

Its working.

Re: Need only capitial text data from column

sunny_talwar — Thu, 04 Jun 2015 12:08:00 GMT

Glad we were able to help.

Best,

Sunny