topic Re: SQL map error code not working in QlikView

SQL map error code not working

paulyeo11 — Tue, 18 Nov 2014 15:46:11 GMT

Hi All

I have a SQL script below working fine :-

Pick(Match(kode_reject, 5, 39), 'reject human error', 'reject machine error') AS DESC_REJECT;

Due to too many error code , i decide to change to mapping :-

ApplyMap('SegmentMap', subfield(kode_reject,' ',1), 'Other') AS DESC_REJECT;

SegmentMap:

MAPPING LOAD * INLINE [

Original, New

5,Human error

39,Machine error

A2,Business Partner

];

when i reload script , it does not show any error code , but the DESC_REJECT field all value display null or missing value "-"

any advise where go wrong on below line :-

ApplyMap('SegmentMap', subfield(kode_reject,' ',1), 'Other') AS DESC_REJECT;

Re: SQL map error code not working

paulyeo11 — Tue, 18 Nov 2014 23:19:43 GMT

Hi All

Any one can help on this ?

Paul

Re: SQL map error code not working

maxgro — Tue, 18 Nov 2014 23:37:05 GMT

I get failed to open document on your attachment

maybe you defined the map table after the applymap?

or the name of the mapping table and the name used in ApplyMap are different?

Re: SQL map error code not working

Tue, 18 Nov 2014 23:39:00 GMT

Paul,

As I'm checking your app from the other thread why SegmentMap: table not generated in SQL mode ?

I noticed that you're creating the mapping table at the end of the script.

Mapping tables should be already defined before using the ApplyMap function.

For that you have to promote the "error code" script tab before the "raw data" tab.

Re: SQL map error code not working

maxgro — Tue, 18 Nov 2014 23:44:50 GMT

From Qlik online help

The ApplyMap function is used for mapping any expression to a previously loaded mapping table. The syntax is:

applymap('mapname', expr [ , defaultexpr ] )

where:

mapname is the name of a mapping table that has previously been created through the mapping load or the mapping select statement (see Mapping). Its name must be enclosed by single, straight Quotation Marks in Scripting.

expr is the expression, the result of which should be mapped.

defaultexpr is an optional expression which will be used as a default mapping value if the mapping table does not contain a matching value for expr. If no default value is given, the value of expr will be returned as is.

Examples:

// Assume the following mapping table:

map1:

mapping load * inline [

x, y

1, one

2, two

3, three ] ;

ApplyMap ('map1', 2 ) returns ' two'

ApplyMap ('map1', 4 ) returns 4

ApplyMap ('map1', 5, 'xxx') returns 'xxx'

ApplyMap ('map1', 1, 'xxx') returns 'one'

ApplyMap ('map1', 5, null( ) ) returns NULL

ApplyMap ('map1', 3, null( ) ) returns 'three'

The MapSubstring function is used to map parts of any expression to a previously loaded mapping table. The mapping is case sensitive and non-iterative and substrings are mapped from left to right. The syntax is:

mapsubstring('mapname', expr)

This function can be used for mapping parts of any expression on a previously loaded mapping table. The mapping is case sensitive and non-recursive. The substrings are mapped from the left to the right. Mapname is the name of a mapping table previously read by a mapping load or a mapping select statement (see Mapping). The name must be enclosed by single straight quotation marks. Expr is the expression whose result should be mapped by substrings.

Examples:

// Assume the following mapping table:

map1:

mapping load * inline [

x, y

1, <one>

aa, XYZ

x, b ] ;

MapSubstring ('map1', 'A123') returns 'A<one>23'

MapSubstring ('map1', 'baaar') returns 'bXYZar'

MapSubstring ('map1', 'xaa1') returns 'bXYZ<one>'

Re: SQL map error code not working

paulyeo11 — Wed, 19 Nov 2014 00:00:35 GMT

Hi jp

Yes you are right and have spotted also i cannot place the mapping table before load the raw data , any solution ?

Paul

Re: SQL map error code not working

paulyeo11 — Wed, 19 Nov 2014 00:08:12 GMT

Hi Massimo

Thank you for the documentation , in fact i have use this mapping command for my project before , but this is the first time i try read raw data from SQL , and i am not sure why SQL not allow create mapping table before load raw data from SQL , as i place the mapping table before loading SQL data i get error , any way to avoid it ?

Paul