topic Re: Mapping or what to replace a value in one table with the proper from another table in QlikView

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

Tue, 10 Jan 2017 06:56:51 GMT

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

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-

...

]

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

jonathandienst — Tue, 10 Jan 2017 07:06:43 GMT

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,

...

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

Tue, 10 Jan 2017 07:51:44 GMT

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

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

jonathandienst — Tue, 10 Jan 2017 08:05:35 GMT

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.

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

Tue, 10 Jan 2017 08:13:09 GMT

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

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

Tue, 10 Jan 2017 08:48:48 GMT

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