Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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-
...
]
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.
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,
...
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 -
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.
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
Once I found my typo, that worked - thanks for talking me through my thoughts.