topic If(Match) not working correctly in QlikView

If(Match) not working correctly

cbaqir — Tue, 25 Sep 2018 15:13:46 GMT

I am trying to use this as a dimension but I am getting some duplicates where it shows both Project and Non-Project and not sure why:

=if(match(COM_CAT,'ERC - Project'),'Project','Non-Project')

I also tried is in the script but same issue: if(wildMatch("COMMITTEE_CAT",'ERC - Project')>0,'Project','Non-Project') as PROJECT

For this ticket, I would only expect Project since it did hit the ERC - Project status.

Re: If(Match) not working correctly

stigchel — Tue, 25 Sep 2018 15:23:02 GMT

Maybe use a mapping load for ID where COM_CAT='ERC - Project' and an applymap('YourMap',ID,'Non-Project') as Project

Re: If(Match) not working correctly

stigchel — Tue, 25 Sep 2018 15:29:42 GMT

Something like

Project:

Mapping load DEFECT_ID,'Project' as To From Source where COMMITTEE_CAT='ERC - Project'

Load DEFECT_ID,Applymap('Project', DEFECT_ID,'Non-Project') as Project from Source

Re: If(Match) not working correctly

joseph_eftamand — Tue, 25 Sep 2018 15:31:35 GMT

Do you have to use match?

Since there is only one string in your condition you could just do

if(COM_CAT = 'ERC - Project','Project','Non-Project')

perhaps you need to use trim(COM_CAT) there may be empty spaces around the text string.

Joseph

Re: If(Match) not working correctly

vishsaggi — Tue, 25 Sep 2018 15:31:57 GMT

May be try like

=if( COMMITTEE_CAT = 'ERC - Project' , 'Project', 'Non-Project')

Re: If(Match) not working correctly

cbaqir — Tue, 25 Sep 2018 15:44:00 GMT

I used an alias for COMMITTEE_CAT called COM_CAT

Re: If(Match) not working correctly

cbaqir — Tue, 25 Sep 2018 15:44:20 GMT

That's what I tried originally but it didn't work either.

Re: If(Match) not working correctly

vishsaggi — Tue, 25 Sep 2018 15:46:31 GMT

Are you using this in expression or in the script. Can you share some sample file to look into?

Re: If(Match) not working correctly

manoj217 — Tue, 25 Sep 2018 15:47:50 GMT

=if(COM_CAT='ERC - Project','Project','Non-Project')

Re: If(Match) not working correctly

joseph_eftamand — Tue, 25 Sep 2018 15:52:40 GMT

‘I used an alias for COMMITTEE_CAT called COM_CAT’

Are you implementing the conditional statement in the same load statement as the alias is being created? In that case you would have to use the original name as the Alias is not applied in the load statement, or use a preload statement.

Joseph Eftamandilos | Consultant

DDI +353 1 272 7706 |Main +353 1 272 7700 Ext. 7706

joseph.eftamandilos@capventis.com<mailto:joseph.eftamandilos@capventis.com> | www.capventis.com<http://www.capventis.com/>

IRL: Building 4, Cherrywood Business Park, Dublin, D18 K7W4, Ireland

UK: 3rd Floor, 26 Finsbury Square, London EC2A 1DS, United Kingdom

<http://www.linkedin.com/company/capventis>[cid:image002.png@01CFABE5.57F50690]<https://twitter.com/capventis>

Re: If(Match) not working correctly

captain89 — Tue, 25 Sep 2018 16:50:34 GMT

Hi,

I think that you should trim your field:

try this:

map_project:

mapping LOAD Committee_Cat, Project INLINE [

Committee_Cat, Project

ERC - Project, Project

];

yourtable:

load*,

ApplyMap('map_project', text(trim(COMMITTEE_CAT)), 'Non-Project') as PROJECT

resident yourtablew;

drop table yourtablew;

Also you can use wildmatch with * at the end:

if(wildMatch("COMMITTEE_CAT",'ERC - Project*')>0,'Project','Non-Project') as PROJECT

or if(trim(COMMITTEE_CAT)='ERC - Project', 'Project', 'Non-Project') as Project