topic Re: Transforming data with Mapping in QlikView

Transforming data with Mapping

Thu, 20 Aug 2015 13:51:31 GMT

Hi guys,

I have a table of data like this:

Item	Count(Item)
O	100
U	200
A	300
P	400

I want to replace P with Present in the load script. So I have done:

MarksMap:

MAPPING LOAD * INLINE [

P, Present

];

at the top of the load script and then:

LOAD *,

ApplyMap('MarksMap', Mark) Inline;

SQL SELECT...

in my preload script before I actually load the data from the database. But the value stays at 'P' in the table.

What am I doing wrong?

Re: Transforming data with Mapping

Anonymous — Thu, 20 Aug 2015 13:55:19 GMT

try this (Change in bold)

BUT check: your table says that field is Item, so in applymap Item instead of Mark should be used!

MarksMap:

MAPPING LOAD * INLINE [

P, Present

];

at the top of the load script and then:

LOAD *,

ApplyMap('MarksMap', Mark) as Present;

SQL SELECT...

Re: Transforming data with Mapping

Thu, 20 Aug 2015 13:55:30 GMT

Have you tried assign a column name to the fields in your table

e.g.

MarksMap:

MAPPING LOAD * INLINE [

Heading, Description

P, Present

];

Re: Transforming data with Mapping

Anonymous — Thu, 20 Aug 2015 13:57:54 GMT

Here you go

MarksMap:

MAPPING LOAD * INLINE [

Col1, Col2

P, Present

];

at the top of the load script and then:

LOAD *,

ApplyMap('MarksMap', Mark) as Present

;

SQL SELECT...

Edit: The first row of your inline table will always be the column names separated by a comma. The rest of the rows will be the data.

Re: Transforming data with Mapping

Anonymous — Thu, 20 Aug 2015 14:04:44 GMT

Iam not really sure if this helps. your code is a little confusing.

can you post an example qvw?

Re: Transforming data with Mapping

Thu, 20 Aug 2015 14:08:03 GMT

It's OK, all the answers on here helped me figure it out.

My load map now looks like this:

MarksMap:

MAPPING LOAD * INLINE [

Mark, Description

P, Present,

A, Authorised,

U, Unauthorised,

O, Offsite

];

and my preload:

LOAD *,

ApplyMap('MarksMap', Mark, null()) as Description;

Thanks to everyone for their help; this community is fantastic!