replacing value from table "like vlookup in excel"

[#1]

Hello

I have a table with region year and labors like this

Region	Year	labors
AP-GCSS-DA	2015	5215
EMEA-GCSS-DA	2015	5833
LAR-GCSS-DA	2015	2940
NA-GCSS-DA	2015	3797
AP-GCSS-DA	2014	3319
EMEA-GCSS-DA	2014	2564
LAR-GCSS-DA	2014	1285
NA-GCSS-DA	2014	4880
AP-GCSS-DA	2013	1435
EMEA-GCSS-DA	2013	4173
LAR-GCSS-DA	2013	5552
NA-GCSS-DA	2013	5720
AP-GCSS-DA	2012	4754
EMEA-GCSS-DA	2012	3678
LAR-GCSS-DA	2012	3267
NA-GCSS-DA	2012	3100
AP-GCSS-DA	2011	5052
EMEA-GCSS-DA	2011	1794
LAR-GCSS-DA	2011	1697
NA-GCSS-DA	2011	3607

the proble that I would like to see in qlikview instead of

AP-GCSS-DA ==> APJ

EMEA-GCSS-DA ==> EMEA

LAR-GCSS-DA ==> LAR

NA-GCSS-DA ==> NA

How I can do it from the import?

thanks

Franck

[jonathandienst]

If you want to include only the text up to the first dash, load like this:

     SubField(Region, '-', 1) As Region

[jonathandienst]

If you need a more complex mapping, then use ApplyMap (which the nearest QV equivalent to vlookup):

MapRegions:

Mapping LOAD *

Inline

[

  Region, Region2

  AP-GCSS-DA, APJ

  EMEA-GCSS-DA, EMEA

  LAR-GCSS-DA, LAR

  NA-GCSS-DA, NA

  ...

];

Data:

LOAD ....

  Region As OriginalRegion,

  ApplyMap('MapRegions', Region) As Region

  ....

[#4]

thanks

In this simple example it's working fine.

but I am trying to insert this code in another file where I have

SQL SELECT region ...

from

it's not working there!!

I believe that the problem is with the SQL select

Do you have an idea how I can implement your code.

I would like to keep my original SQL select.

thanks

[Anonymous · Accepted solution]

SQL doesn't understand QV functions, including applymap.  The fix is simple, use preceding load:

Data:

LOAD

  region As OriginalRegion,

  ApplyMap('MapRegions', region) As Region

...

;

SQL SELECT region ...

from

[#6]

Hi

it's working fine

thanks