# How to map field values into other values

**URL:** <https://forum.cloverdx.com/t/how-to-map-field-values-into-other-values/1197>\
**Category:** CloverDX Platform\
**Created:** [March 29, 2013, 12:00am UTC](https://forum.cloverdx.com/t/how-to-map-field-values-into-other-values/1197 "2013-03-29T00:00:00Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![zram\_k](https://avatars.discourse-cdn.com/v4/letter/z/dc4da7/32.png) [@zram\_k](https://forum.cloverdx.com/u/zram_k)\
**Post date:** [March 29, 2013, 12:00am UTC](https://forum.cloverdx.com/t/how-to-map-field-values-into-other-values/1197/1 "2013-03-29T00:00:00Z")

</div>

I am new to CloverETL, QuickStart was awesome, but need more help. I need to map records in various fields based on their values and label the null or unknown values as “Unknown”.

For example: A client file with Race information. Change caucasian to **WHITE** , change African American to **BLACK** , everything else to **UNKNOWN** n (including null)

I know I can use a LookUps to do part of this, but as it seems, LookUps are only 1-to-1. I need to be able to replace all records that fall outside of the 1-to-1 and also null records.

---

<div class="post-metadata">

**Author:** ![slechtaj](https://avatars.discourse-cdn.com/v4/letter/s/59ef9b/32.png) [@slechtaj](https://forum.cloverdx.com/u/slechtaj)\
**Post date:** [April 2, 2013, 9:47am UTC](https://forum.cloverdx.com/t/how-to-map-field-values-into-other-values/1197/2 "2013-04-02T09:47:09Z")

</div>

Hi,

You can use LookupJoin in which you will need to set “Left outer join” attribute to true. As you can see in transform editor (under transformation attribute), the component has two inputs - $in.0 represents input records from the connected edge; the other one ($in.1) is used for joined records from lookup table. When left outer join is turned on, the component will return also those records that have not been found in lookup table. To map a particular value to fields that has not been found in lookup, you will only need to check whether the value from input port $in.1 is null (in transform editor – available under transformation attribute). See the sample CTL code below (adjust it accordingly to your use case).

```auto
function integer transform() {
	if(isnull($in.1.key)) {
		$out.0.outField = "UNKNOWN";
	} else {
		$out.0.outField = $in.1.value;
	}
	return OK;
}  

```

---

<div class="post-metadata">

**Author:** ![zram\_k](https://avatars.discourse-cdn.com/v4/letter/z/dc4da7/32.png) [@zram\_k](https://forum.cloverdx.com/u/zram_k)\
**Post date:** [April 3, 2013, 1:42pm UTC](https://forum.cloverdx.com/t/how-to-map-field-values-into-other-values/1197/3 "2013-04-03T13:42:22Z")

</div>

Thank you slechtaj, your suggestion absolutely helped. There is however 1 more case that you did not address. The case where the lookUp table does not match anything and the field is not null.

Using the same race example as before. if **x = black** , **y = white** and **null = unknown** , what if the value is z or something other than what I have identified in the lookup.

I am trying to match all cases. If data comes in and it has not been identified in the lookup I would like to be able to mark it as “Unknown”.

Also, is there a fast way for me to use the same lookup on multiple fields without using a new component.

---

<div class="post-metadata">

**Author:** ![imriskal](https://avatars.discourse-cdn.com/v4/letter/i/b38774/32.png) [@imriskal](https://forum.cloverdx.com/u/imriskal)\
**Post date:** [April 4, 2013, 9:27am UTC](https://forum.cloverdx.com/t/how-to-map-field-values-into-other-values/1197/4 "2013-04-04T09:27:54Z")

</div>

Hi, Zram\_K,

You can just add a new condition into if, searching for keys non-existing in your lookup.

```auto
if(isnull($in.1.key) || lookup(my_lookup).count($in.1.key) == 0) {
	// Do something.
}

```

And regarding your next question about multiple fields, I am not exactly sure what you mean. Can you please rephrase and describe that? If your lookup contains everything needed for another field, you can use it in Transform editor either in Transform GUI or in source CTL (see [http://doc.cloveretl.com/documentation/ … -ctl2.html](http://doc.cloveretl.com/documentation/UserGuide/topic/com.cloveretl.gui.docs/docs/lookup-table-functions-ctl2.html))

Best regards,

---

<div class="post-metadata">

**Author:** ![zram\_k](https://avatars.discourse-cdn.com/v4/letter/z/dc4da7/32.png) [@zram\_k](https://forum.cloverdx.com/u/zram_k)\
**Post date:** [April 4, 2013, 4:40pm UTC](https://forum.cloverdx.com/t/how-to-map-field-values-into-other-values/1197/5 "2013-04-04T16:40:12Z")

</div>

Now that I have had more time test, it does not seem like the first answer nor the most recent is doing what I am asking. So let me start by reiterate what I am trying to do:

My Ethnicity lookup renames fields based on a key.  
**1.** When incoming data fields are null = “Unknown”.  
**2.** When incoming data fields are not-null, but not in the lookup = “Other”.

Here is the code I’ve created based on CloverCare Support:

```auto

if(isnull($in.1.Old_Ethnicity) || lookup(Ethnicity).count($in.1.Old_Ethnicity) == 0){
      $out.0.Ethnicity = "Unknown";
   } else {
      $out.0.Ethnicity = $in.1.New_Ethnicity;
   }

```

Seems like all exceptions = “Unknown” regardless of null or missing key.

Thank you in advance for the help.

---

<div class="post-metadata">

**Author:** ![David\_Hlidek](https://avatars.discourse-cdn.com/v4/letter/d/58f4c7/32.png) [@David\_Hlidek](https://forum.cloverdx.com/u/David_Hlidek)\
**Post date:** [April 5, 2013, 7:42am UTC](https://forum.cloverdx.com/t/how-to-map-field-values-into-other-values/1197/6 "2013-04-05T07:42:42Z")

</div>

Hi,

Well, in previous posts you never mentioned “Other” as possible output. Considering that, your code should probably look like:

```auto

//ethnicity not set
if (isnull($in.0.Ethnicity)) {
  $out.0.Ethnicity = "Unknown";
//ethnicity not presented in lookup
} else if ( lookup(Ethnicity).count($in.0.Ethnicity)<=0) {
  $out.0.Ethnicity = "Other";
//ethnicity set and presented in lookup
} else {
  //take value from lookup
  $out.0.Ethnicity = $in.1.New_Ethnicity;
}

```