Skip to main content
Announcements
Global Transformation Awards submissions are open! SUBMIT YOUR STORY
cancel
Showing results for 
Search instead for 
Did you mean: 
slavomir_garaj
Contributor

ApplyMap not working on data from DB

I applied the ApplyMap function to a data sample loaded via an Inline statement. Everything there works as it should.
But when I retrieve the same data from an SQL query, ApplyMap does not work for values that contain only numeric characters.
If I add the trim or substr function the mapping works correctly. Can you please explain why the behavior is different with SQL?

My script:

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;-$#,##0.00';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET FirstWeekDay=6;
SET BrokenWeeks=1;
SET ReferenceDay=0;
SET FirstMonthOfYear=1;
SET CollationLocale='en-US';
SET CreateSearchIndexOnReload=1;
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';
SET NumericalAbbreviation='3:k;6:M;9:G;12:T;15:P;18:E;21:Z;24:Y;-3:m;-6:μ;-9:n;-12:p;-15:f;-18:a;-21:z;-24:y';
 
MyMapping:
Mapping Load * 
Inline [
Code, Value
1100, one one zero zero
0200, zero two zero zero
0, false
1, true
Y, Yes
N, No
] ;
 
Qualify *;
 
MyInlineTable:
LOAD Id,
     SrcCode,
     ApplyMap('MyMapping', SrcCode, 'NOT MAPPED') As MappedValue,
     len(SrcCode) as SrcCodeLength
Inline [
Id, SrcCode 
1, 0000
2, 1100
3, 0200 
4, 1
5, 0
6, Y 
7, N
];
 
LIB CONNECT TO 'ORACLE_DB_12.2';
 
MySqlTable:
LOAD Id,
     SrcCode,
     ApplyMap('MyMapping', SrcCode, 'NOT MAPPED') As MappedValue,
     len(SrcCode) as SrcCodeLength;
SQL
select 1 as "Id", '0000' as "SrcCode" from dual union all
     select 2 as "Id", '1100' as "SrcCode" from dual union all
     select 3 as "Id", '0200' as "SrcCode" from dual union all
     select 4 as "Id", '1' as "SrcCode" from dual union all
     select 5 as "Id", '0' as "SrcCode" from dual union all
     select 6 as "Id", 'Y' as "SrcCode" from dual union all
     select 7 as "Id", 'N' as "SrcCode" from dual;
 
MySqlTableTrimmed:
LOAD Id,
     SrcCode,
     ApplyMap('MyMapping', Trim(SrcCode), 'NOT MAPPED') As MappedValue,
     len(SrcCode) as SrcCodeLength;
SQL
select 1 as "Id", '0000' as "SrcCode" from dual union all
     select 2 as "Id", '1100' as "SrcCode" from dual union all
     select 3 as "Id", '0200' as "SrcCode" from dual union all
     select 4 as "Id", '1' as "SrcCode" from dual union all
     select 5 as "Id", '0' as "SrcCode" from dual union all
     select 6 as "Id", 'Y' as "SrcCode" from dual union all
     select 7 as "Id", 'N' as "SrcCode" from dual;
 
slavomir_garaj_0-1710950492082.pngslavomir_garaj_1-1710950542399.png

 

 

Labels (3)
4 Replies
RafaelBarrios
Partner - Specialist

hi @slavomir_garaj 

check the fields values format, i think you are mapping a text value  '0000',  '0001'.... with number values in your mapping table

here, you can see qlik its understanding values as numbers, thats why they are aligned to the right

RafaelBarrios_0-1711013653937.png

 

while here are alinged to left, that means qlik understand they are text

RafaelBarrios_1-1711013711078.png

 

 

 

Hpe this helps.

Best

help users find answers! Don't forget to mark a solution that worked for you & to smash the like button! 

slavomir_garaj
Contributor
Author

Thanks. Yes, I also thought it was a problem of different data types. I've got more questions...

1) How to "check the fields values format"?

2) Why does Trim on database field work? Does the data type change there?

3) Is there a safe way to map number like string values via ApplyMap function?

marcus_sommer

Qlik hasn't data-types else does just a data-interpretation in numbers or strings or mixed. This could be easily checked with functions like: isnum(), istext() or isnull() respectively by not real NULL with len(trim()). Checking the format of a value is instead quite hard and could be often avoided with other measures.

By applying string-functions you should expect that the result is also a string even if there might be exceptions in regard to the used function or by other dependencies.

And of course you could ensure that your calculations are working regardless of the input is a number or a string. The simplest approach would be to define it already by loading the raw-data, maybe in this way:

load *, applymap('MyMap', Field_Y, 'no match') as Field_Z;
load *, text(Field_X) as Field_Y;
sql select * from ...;

respectively within the first QVD storing layer and for each relevant fields from the various loads in the same way because it avoids to touch this matter in any following logic.

Beside of this you could also apply converting and formatting functions within the function-call like:

applymap('m', text(MyField), 'no match')

or nesting multiple applymap() like:

applymap('mNumber', MyField, applymap('mString', MyField, 'no match'))

or also loading all numbers and string-values within a single mapping-table.

RafaelBarrios
Partner - Specialist

hi again @slavomir_garaj 

1) How to "check the fields values format"?

quick way, if the values are aligned to the left side the value is text, while it is aligned right it is a number value

in you case you have mix in same column, but when all values are same type you can select the field and qlik will show you

RafaelBarrios_1-1711030842566.png

 

2) Why does Trim on database field work? Does the data type change there?

TRIM only drops whitespaces

 

3) Is there a safe way to map number like string values via ApplyMap function?

you can force Qlik to understand the fields as you need by using

num#(field,'0000')

https://help.qlik.com/en-US/qlikview/May2023/Subsystems/Client/Content/QV_QlikView/Scripting/Interpr...

 

in your case, i would recommend something like this:

in the mapping table force qlik to understand and then change it to text

MyMapping:
load
   text(num#([num field],'0000')) as [num field], //force all numbers to 0000 format and change to text
   [another field]
inline [
num field,another field
...
...
....];

and for the SQL load

MySqlTableTrimmed:

LOAD Id,
     SrcCode,
     ApplyMap('MyMapping',text(num(num#(SrcCode,'0000'),'0000')), 'NOT MAPPED') As MappedValue,
     len(SrcCode) as SrcCodeLength;
SQL
select 1 as "Id", '0000' as "SrcCode" from dual union all
     select 2 as "Id", '1100' as "SrcCode" from dual union all
     select 3 as "Id", '0200' as "SrcCode" from dual union all
     select 4 as "Id", '1' as "SrcCode" from dual union all
     select 5 as "Id", '0' as "SrcCode" from dual union all
     select 6 as "Id", 'Y' as "SrcCode" from dual union all
     select 7 as "Id", 'N' as "SrcCode" from dual;
 
num#() tells qlik to understand number as 0000
num() tells Qlik to change 1 and 0 to 0001 and 0000
text change everything to text

this will make you another problem because of Y and N values so change it with
LOAD Id,
     SrcCode,
     ApplyMap('MyMapping',formated_SrcCode, 'NOT MAPPED') As MappedValue,
     len(SrcCode) as SrcCodeLength;
LOAD *,
if(IsNum([num field]),
text(num(num#(SrcCode,'0000'),'0000')), //understand & change numeric to text
SrcCode) //already text values
as formated_SrcCode;
SQL
select 1 as "Id", '0000' as "SrcCode" from dual union all
     select 2 as "Id", '1100' as "SrcCode" from dual union all
     select 3 as "Id", '0200' as "SrcCode" from dual union all
     select 4 as "Id", '1' as "SrcCode" from dual union all
     select 5 as "Id", '0' as "SrcCode" from dual union all
     select 6 as "Id", 'Y' as "SrcCode" from dual union all
     select 7 as "Id", 'N' as "SrcCode" from dual;
 

Hope this helps

help users find answers! Don't forget to mark a solution that worked for you & to smash the like button!