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;

[vamshi_1241]

Hi Sunny, Thanks for your response. But why is it showing different when 0 to 91 in the mapping load?

That's my requirement - wherever I get the country code 0, I have to replace that with 91.

First Applymap is passing values 0 and 9 (Left1 field) to mapping table, since there is no 9 in load, it will go to the second applymap and here Left2 field passing values 09, 91 and 99. I am not doing any mapping for 09 but still it returns 91 as output instead 09 and 91.

[vamshi_1241]

Left1 has two values 0, 9 -

The first part of applymap is true : Left =0 and returns 91.

The first part of applymap is also false: Left1 = 9, there is no mapping for 9 in the load. So, it go to second part of applymap where we are passing Left2 (it has three values - 09,91 and 99). Here,  no mapping for 09, so it should give us 09 as output.

[Vladislav_qv_dev · Accepted solution]

@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

[vamshi_1241]

Ok. If you can add the else part in second applymap like ‘not matched’, and see what you get here? You get Not Matched and 91.. how Not Matched will show up here if you have only 99 and 91 values in left2?

[sunny_talwar · Accepted solution]

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?

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

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?

[vamshi_1241]

Thanks @sunny_talwar  and @Vladislav_qv_dev . I am all set now.

[sunny_talwar]

All set how? What was the issue? How did you fix it?