Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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,
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
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,
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
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
];