Applymap issue?

[vamshi_1241]

Hello Everyone,

Can you help me understand the Applymap results for the field 'Final Code' from below script - (I can use if statement, but thought of trying mapping load here). I was thinking that the Final Code field must return 09 and 91 as output, but the Qlik is done differently here-

CodeMap:
Mapping Load * Inline
[
OldCode, NewCode
91, 91
0, 91
99, 91
];

test:
Load *,
// if(left(KeepChar_PhoneNumber, 2) = 91, 91,
// if(Left(KeepChar_PhoneNumber, 1) = 0, 91,
// if(Left(KeepChar_PhoneNumber, 2) = 99, 91))) as Code;

Applymap('CodeMap', Left1) as Cod1, //91, 9
ApplyMap('CodeMap', Left2) as Code2 //09,91
;
Load *,
Len(KeepChar_PhoneNumber) as len_KeepChar_PhoneNumber,
right(KeepChar_PhoneNumber, 10) as Phone#, // 9912223415
left(KeepChar_PhoneNumber, 1) as Left1, // 0, 9
left(KeepChar_PhoneNumber, 2) as Left2 // 09, 91, 99
;

Load PhoneNumber,
KeepChar(PhoneNumber, '0123456789') as KeepChar_PhoneNumber
Inline
[
PhoneNumber
+91-99.122.234.15
991.222.3415
0 99 122 234 15
+91-9912223415
+91/99~122.234.15
@9912223415
@0.9912223415
#0-991.222.3415
0 99 122 234 15
+91-9912223415
];

temp:
NoConcatenate
load *,
Applymap('CodeMap', Left1, Applymap('CodeMap', Left2)) as Final_Code   //09, 91
Resident test
order by Code2 asc;

drop table test;

exit Script;

[Vladislav_qv_dev]

Hi, @vamshi_1241 

I think that Final Code doesn't have '09', because your applymap-table (CodeMap) just doesn't have data for '09' condition

[vamshi_1241]

But, if nothing is matched in the mapping table, it should return the same value as output, right? In this case, mapping load doesn’t have 09, so it should return 09 in the result as second applymap is not having else part.

[Vladislav_qv_dev]

No, if there are no matches, then ApplyMap returns null-value.

If you want to change that, you should add a 3rd argument to the function, like

ApplyMap('CodeMap',  Left1,  Left1)

[vamshi_1241]

if that is the case - you should see null and 91 instead 09 and 91 with below code 

ApplyMap('CodeMap', Left2) as Code2 

[vamshi_1241]

If I add else part like 'Not matched' in the second applymap - it is giving output as 'Not Matched' and 91 - Which means without else part, it should return 09 as output.

temp:
NoConcatenate
load *,
Applymap('CodeMap', Left1, Applymap('CodeMap', Left2, 'Not Matched')) as Final_Code   //09, 91
Resident test
order by Code2 asc;

drop table test;

[Vladislav_qv_dev]

>>Which means without else part, it should return 09 as output.

If there are no matches, it should return null as output

[vamshi_1241]

Have you tested this - ApplyMap('CodeMap', Left2) as Code2 

[sunny_talwar]

I think the problem is that you have a mapping for 0 to 91... if you try this... then you should get what you want

CodeMap:
Mapping
LOAD * INLINE [
    OldCode, NewCode
    91, 91
    99, 91
];
//0, 91

test:
LOAD *,
	 Applymap('CodeMap', Left1) as Cod1, //91, 9
	 ApplyMap('CodeMap', Left2) as Code2; //09,91
LOAD *,
	 Len(KeepChar_PhoneNumber) as len_KeepChar_PhoneNumber,
	 right(KeepChar_PhoneNumber, 10) as Phone#, // 9912223415
	 left(KeepChar_PhoneNumber, 1) as Left1, // 0, 9
	 left(KeepChar_PhoneNumber, 2) as Left2; // 09, 91, 99
LOAD PhoneNumber,
	 KeepChar(PhoneNumber, '0123456789') as KeepChar_PhoneNumber;
LOAD * INLINE [
    PhoneNumber
    +91-99.122.234.15
    991.222.3415
    0 99 122 234 15
    +91-9912223415
    +91/99~122.234.15
    @9912223415
    @0.9912223415
    #0-991.222.3415
    0 99 122 234 15
    +91-9912223415
];

temp:
NoConcatenate
LOAD *,
	 Applymap('CodeMap', Left1, Applymap('CodeMap', Left2)) as Final_Code,   //09, 91
	 Applymap('CodeMap', Left1, Applymap('CodeMap', Left2, 'Not Matched')) as Final_Code_2
Resident test
Order By Code2 asc;

DROP Table test;

[Vladislav_qv_dev]

I've tested the code one more time.

Sorry, I always thought that if there are no matches, it will return null, but documentation says that the value will be returned as is (https://help.qlik.com/en-US/qlikview/May2021/Subsystems/Client/Content/QV_QlikView/Scripting/Mapping...)

So now, I don't see any problems. Final code doesn't return '09', because first part of ApplyMap is true: Left1 = 0 and ApplyMap returns '91' as you pointed out in the map table