topic Re: Applymap issue? in QlikView

Applymap issue?

vamshi_1241 — Thu, 24 Jun 2021 14:43:03 GMT

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;

Re: Applymap issue?

Vladislav_qv_dev — Thu, 24 Jun 2021 14:55:25 GMT

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

Re: Applymap issue?

vamshi_1241 — Thu, 24 Jun 2021 14:58:47 GMT

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.

Re: Applymap issue?

Vladislav_qv_dev — Thu, 24 Jun 2021 15:02:30 GMT

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)

Re: Applymap issue?

vamshi_1241 — Thu, 24 Jun 2021 15:17:16 GMT

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

ApplyMap('CodeMap', Left2) as Code2

Re: Applymap issue?

vamshi_1241 — Thu, 24 Jun 2021 15:23:10 GMT

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;

Re: Applymap issue?

Vladislav_qv_dev — Thu, 24 Jun 2021 15:40:58 GMT

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

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

Re: Applymap issue?

vamshi_1241 — Thu, 24 Jun 2021 16:26:19 GMT

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

Re: Applymap issue?

sunny_talwar — Thu, 24 Jun 2021 16:48:54 GMT

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;

Re: Applymap issue?

Vladislav_qv_dev — Thu, 24 Jun 2021 19:55:27 GMT

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/MappingFunctions/ApplyMap.htm)

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

Re: Applymap issue?

vamshi_1241 — Fri, 25 Jun 2021 02:25:14 GMT

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.

Re: Applymap issue?

vamshi_1241 — Fri, 25 Jun 2021 04:41:38 GMT

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.

Re: Applymap issue?

Vladislav_qv_dev — Fri, 25 Jun 2021 05:44:08 GMT

@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

Re: Applymap issue?

vamshi_1241 — Fri, 25 Jun 2021 09:28:07 GMT

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?

Re: Applymap issue?

sunny_talwar — Fri, 25 Jun 2021 13:39:09 GMT

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?

Re: Applymap issue?

vamshi_1241 — Fri, 25 Jun 2021 14:02:42 GMT

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

Re: Applymap issue?

sunny_talwar — Fri, 25 Jun 2021 14:33:24 GMT

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