- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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:
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
while here are alinged to left, that means qlik understand they are text
Hpe this helps.
Best
help users find answers! Don't forget to mark a solution that worked for you & to smash the like button!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
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')
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;
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!