topic Re: ApplyMap in a SQL statement in QlikView

ApplyMap in a SQL statement

Wed, 27 Jan 2016 15:43:04 GMT

I am pulling data from a SQL database into Qlikview and attempting to Apply a Mapping.

ErrorSource: Microsoft SQL Server Native Client 11.0, ErrorMsg: 'ApplyMap' is not a recognized built-in function name.

I'm guessing this message is because I am attempting to use 'ApplyMap' in the select statement and it is not a SQL function. Does someone know of another way to accomplish this?

Thank You

Re: ApplyMap in a SQL statement

alexpanjhc — Wed, 27 Jan 2016 16:19:20 GMT

In the SQL codes(where you connect Qlik to your DB) itself, you can't use applymap. use after you pull the tables.

Re: ApplyMap in a SQL statement

sunny_talwar — Wed, 27 Jan 2016 16:22:12 GMT

Try doing it in the LOAD statement (preceding load) to use ApplyMap because ApplyMap is a QlikView function.

Qlik Design Blog : Preceding Load | Qlik Community

Re: ApplyMap in a SQL statement

swuehl — Wed, 27 Jan 2016 16:22:49 GMT

You can use a preceding LOAD statement:

LOAD

FIELD,

ApplyMap('MAP', FIELD, 'No Map found') as MappedField;

SELECT FIELD FROM ...;

Re: ApplyMap in a SQL statement

Wed, 27 Jan 2016 17:23:46 GMT

The Preceding load worked but my following SELECT statement did not pull in the rest of the data,

LOAD

FIELD,

ApplyMap('MAP', FIELD, 'No Map found') as MappedField;

SELECT FIELD, Field2, field3,

FROM ...;

I only get the MAP and FIELD.

O think I'm on the right track though. thanks,

Re: ApplyMap in a SQL statement

maxgro — Wed, 27 Jan 2016 17:57:27 GMT

if you want also the other 2 fields, add the 2 fields to the load (in bold)

LOAD

FIELD,

Field2, field3,

ApplyMap('MAP', FIELD, 'No Map found') as MappedField;

SELECT FIELD, Field2, field3,

FROM ...;

Re: ApplyMap in a SQL statement

Wed, 27 Jan 2016 19:19:31 GMT

Thank you all. Very helpful!