using applymap along with if condition

[Anonymous]

Hi

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.

[danansell42]

Hi

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

[Anonymous]

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

[danansell42 · Accepted solution]

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

    b,

    c, 1

    d, 1

    e, 1

    f,

    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;

[MindaugasBacius]

Then I guess the only problem here is Applymap because:

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

Works perfectly.

[rubenmarin]

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

[Anil_Babu_Samineni]

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]

[Anonymous]

thanks..It worked correctly

[Anonymous]

Thanks the last option worked