Mapping wildcard

[ValeriaBonini]

Hi, I have a table ( MY_TABLE) where I load data from a QVD that have an order number of 6 characters and a item name.

I want a new column with "0" if the order number begins with "EF3" "OL8" or "1" if the order numbers begin with "O5R" "OM4"

How can i use a mapping table to do that?

[Oleg_Troyansky]

Hi Valeria,

Absolutely! You can define a mapping table, where you can list all 3-character values and their corresponding flag field values (0/1), and then in the process of data load, apply the map to the first 3 characters of the Order number - something like this:

MyMap:

MAPPING LOAD * INLINE [

Prefix, Flag

EF3, 0

OL8,0

O5R,1

OM4,1

];

Data:

LOAD

...

ApplyMap('MyMap', Left(OrderNumber,3))  as MyFlag

...

;

Notice that I didn't specify any default values, so if some orders should start from any other 3-character strings, they will appear in the same field unmapped, calling for attention. Or, you can add the default value as the third parameter to the ApplyMap function, to force a certain default value.

If you'd like to learn more advanced development techniques, join us at the Masters Summit for Qlik - coming to New Orleans on November 14-16.

Cheers, 

[ValeriaBonini]

If i do that i obtain 

ordernumber  myflag

EF3erwer          EF3

OL8eqwe           OL8

O5Rdfef            O5R

OM4afdaf        OM4

and not 

ordernumber    myflag

EF3                    0

OL8                   0

O5R                   1

OM4                   1

[Oleg_Troyansky]

Valeria,

It looks like the mapping logic didn't work as expected. Could you post an exact copy of your script? I'll try to troubleshoot it for you.

Cheers,

[vinieme12]

your field "ordernumber " might have extra spaces which is why the values didn't match use TRIM() to remove extra spaces

Also to keep the value formatting consistent wrap them in UPPER()/LOWER()

as below

MyMap:

MAPPING LOAD  upper(Prefix) as Prefix,Flag INLINE [

Prefix, Flag

EF3, 0

OL8,0

O5R,1

OM4,1

];

Data:

LOAD

...

ApplyMap('MyMap', Left(UPPER(TRIM(OrderNumber)),3) )  as MyFlag

[ValeriaBonini]

I just have a table like this:

MYTABLE:

load

ordernumber,

documenttype

object

from [ ...myqvd]

and i want it like this:

ordernumber       documenttype      object    flag

EF3ERWER           paper                        chair          0

OL8EQWE              electronic               table             0

O5RDFEF               electronic               chair            1

OM4AFDAF              paper                       other            1

the mapping table is:

MyMap:

MAPPING LOAD * INLINE [

Prefix, Flag

EF3, 0

OL8,0

O5R,1

OM4,1

];