Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
vamshi_1241
Partner - Creator
Partner - Creator

Applymap issue?

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;

 

Labels (1)
2 Solutions

Accepted Solutions
Vladislav_qv_dev
Contributor III
Contributor III

@vamshi_1241, Left2 has only 2 values ('99', '91'). If Left2 contains '09', Left1 contains '0', so the first part of ApplyMap will be applied

Vladislav_qv_dev_0-1624599717704.png

 

View solution in original post

sunny_talwar

I am confused.... I agree that we don't have a mapping for 9, but we do have it for 0 (if we don't remove the highlighted line). Do you agree?

sunny_talwar_0-1624626904084.png

So, if we have 0 for Left1... it will be mapped to 91 (first three rows in the below screenshot, agreed?

sunny_talwar_1-1624626986903.png

But 9 doesn't meet the condition.... so then we look at Left2... Left 2 for when Left1 is 9... we have 99 OR 91... and both get mapped to 91... so we get 91 for them too?, do you agree?

What am I missing here? Which rows are not showing correctly?

 

 

 

View solution in original post

16 Replies
Vladislav_qv_dev
Contributor III
Contributor III

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
Partner - Creator
Partner - Creator
Author

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
Contributor III
Contributor III

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
Partner - Creator
Partner - Creator
Author

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
Partner - Creator
Partner - Creator
Author

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
Contributor III
Contributor III

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

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

vamshi_1241
Partner - Creator
Partner - Creator
Author

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
Contributor III
Contributor III

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