topic Re: using applymap along with if condition in QlikView

using applymap along with if condition

Anonymous — Wed, 28 Sep 2016 08:12:35 GMT

I have names that have been populated by users and these are mapped against the std company names using applymap function

I need to identify the tickets when marked against these std company names i.e 'Y' if identified against the std name else 'N'.

I have tried using this function if(isnull(ApplyMap ('maptable1',[Problem Owner],Null()))=-1,'N','Y') as [Referred to CPS] in the script

but this doesnt give me the right result. Names are getting mapped correctly against the mapping table but for the identified names against the std name it still marks as 'N' for few users.

Re: using applymap along with if condition

danansell42 — Wed, 28 Sep 2016 08:32:16 GMT

I think you just need to remove the '=-1'

Therefore should be

if(isnull(ApplyMap ('maptable1',[Problem Owner],Null())),'N','Y') as [Referred to CPS]

Thanks

Dan

Re: using applymap along with if condition

Anonymous — Wed, 28 Sep 2016 08:38:26 GMT

No..it still doesnt work.. When I remove -1 it continues to show N for the std names as well.

Re: using applymap along with if condition

danansell42 — Wed, 28 Sep 2016 08:48:18 GMT

i just done a little test with dummy data and it works fine for me.

I'm assuming there must be something in your data causing the issue.

Does the mapping data have every instance of [Problem Owner] on it but blanks where not available?

Maybe its not creating any NULL values??

Here is the test script i used that worked:

Map:

Mapping LOAD * INLINE [

F1, F2

a, 1

c, 1

d, 1

e, 1

g, 1

];

DataTemp:

LOAD * INLINE [

F1, F2

a, 1

c, 1

d, 1

e, 1

g, 1

];

Data:

NoConcatenate

LOAD *,

applymap('Map',F1,null()) as Mapped,

if(isnull(applymap('Map',F1,null())),'N','Y') as Test

Resident DataTemp;

Drop Table Data;

Re: using applymap along with if condition

MindaugasBacius — Wed, 28 Sep 2016 08:48:48 GMT

Then I guess the only problem here is Applymap because:

=IF(ISNULL(NULL()), 'N', 'Y')

Works perfectly.

Re: using applymap along with if condition

rubenmarin — Wed, 28 Sep 2016 09:05:26 GMT

Hi Sanjyot, the 3rd parameter of applymap is the value the function returns when the value wasn't found in the map table, so I think you need to set the 3rd parameter to '-1' or change the condition of the if to null()

if((ApplyMap ('maptable1',[Problem Owner],-1)=-1,'N','Y') as [Referred to CPS]
if((ApplyMap ('maptable1',[Problem Owner],Null())=Null(),'N','Y') as [Referred to CPS]
if(isnull(ApplyMap ('maptable1',[Problem Owner],Null())),'N','Y') as [Referred to CPS]

Edit: I overlooked the isnull(). Note that IsNull(-1)=0 (False), so when applymap doesn't found any value it returns -1, and the IsNull(-1) returns false, so it will never go to the True part of if (unless something from the map table returns Null())

Re: using applymap along with if condition

Anil_Babu_Samineni — Wed, 28 Sep 2016 09:08:52 GMT

What are the use of -1 in your exression? Try, something like if required

if(isnull(ApplyMap ('maptable1',[Problem Owner],''))=-1,'N','Y') as [Referred to CPS]

Re: using applymap along with if condition

Anonymous — Wed, 28 Sep 2016 12:31:27 GMT

thanks..It worked correctly

Re: using applymap along with if condition

Anonymous — Wed, 28 Sep 2016 12:31:59 GMT

Thanks the last option worked