Mapping or what to replace a value in one table with the proper from another table

[#1]

We have an archaic legacy system that downloads certain negative numbers with an Alphanumeric replacement.   The field that is downloaded is a CODE/ID that is used to pull a description from another table in a join.

I want to use an INLINE table to identify the downloaded alpha with the correct code that can be joined to another table or so we can see what the admin actually entered as they can overwrite descriptions too.

Downloaded data looks like this -

TermCode

0}

12

24

5P

4}

The numeric (12,24) are fine.  The others need replacement

MAPPING

LOAD

* INLINE [

TermCode,RealTermCode

1}, 10-

2}, 20-

3}, 30-

4},40-

5},50-

6},60-

7},70-

8},80-

9},90-

0J,1-

1J,11-

2J,21-

3J,31-

4J,41-

5J,51-

6J,61-

7J.71-

8J,81-

9J,91-

0K,2-

1K,12-

...

]

[jonathandienst]

I am not sure what your question is.

Name your mapping table and you can use ApplyMap() to use the mapping during the load to create a derived field

MapTermCodes:

MAPPING

LOAD

* INLINE [

TermCode,RealTermCode

...

;

Main:

LOAD

    ...

   TermCode as LegacyTermCode,

   ApplyMap('MapTermCodes', TermCode) as TermCode,

   ...

[#3]

I get that part, but also want to preserve the "good" values.   I am thinking I want to do more of a MapSubString type function?

Not sure this is a good example explanation, but think this is what I am looking for -

Using MapSubstring() to edit strings | Qlikview Cookbook

[jonathandienst · Accepted solution]

MapSubString changes a substring within a field, rather than returning the mapped value. Perhaps you are looking for

ApplyMap('MapTermCodes', TermCode, TermCode) as TermCode,

The third parameter is the default when not found in the mapping, so it will use the existing value if it is not in the mapping table.

[#5]

I tried that before your first responnse and it came up with - in the field.   Investigating.   I do think the MapSubString may do the trick.   I am pretty much looking for an "in field" replacement

[#6]

Once I found my typo, that worked - thanks for talking me through my thoughts.