One to many apply map or wildmatch

[MK9885]

I need to map a field which has numeric like 0,1,2,3,4....etc to another field which has A,B,C,D.... etc

To match those I have a description field.

Ex: if I select NY as state

Field 1 value is 3

Field 2 value is C & D

I'd want to make Field 1 look like C & D, Instead of 3 when NYS is selected.

Tried applymap, I think it usually works one on one mapping.

Wildmatch was also not much help either.

Can someone help me to find an expression or something in backend?

@sunny_talwar or if someone can solve this for me?

Thanks.

[Kushal_Chawda]

---

I'd want to make Field 1 look like C & D, Instead of 3 when NYS is selected.

Why you want to make Field1 look like C & D when you already have Field2 with C & D to look at?

[MK9885]

there is a common field as State

So if I select NYS, I get selections as both 3, C&D.

Now I want to map number 3 (field1) to C&D value (field2)

Basically elimination Field 1 & mapping 3 to C & D.

[Kushal_Chawda]

something like below?

3-C

3-D

[MK9885]

See attached document.

You'll see Commercial has 3 codes. I just want 2 (I &J)

This happened because I concatenated Code field1 with Code field2

Name field is same for both.

Apply map or wildmatch? 

[Kushal_Chawda]

So instead concatenate can't you perform join on Name?

[MK9885]

No I cannot.

It's not just 1 field.. 

Both the tables are coming from 2 different DB's. but have similar field names.

So I concatenated to make them in 1 fact table.

When I did that, One DB has Numerics & other has Alphabets.

But I want numerics to look like alphabets

[Kushal_Chawda]

try something like below

Code:

load Name,

          Code as CODE

resident Table

where  istext(Code);

left join(Code)

load Name,

          Code as CODE2

resident table

where isnum(Code);

use highlighted Code

[MK9885]

Instead of creating a new table, can I not use Applymap or any other if function?

This is just an 1 example, there are quite a few fields where I'd need to do mapping...

[Kushal_Chawda · Accepted solution]

Well your data model design is in a way that applymap won't work here as I already advice you to join the table instead concatenate. Applymap work similar to join.