# Reformat node

**URL:** <https://forum.cloverdx.com/t/reformat-node/567>\
**Category:** CloverDX Platform\
**Created:** [August 8, 2009, 12:00am UTC](https://forum.cloverdx.com/t/reformat-node/567 "2009-08-08T00:00:00Z")\
**Posts on this page:** 14\
**Page:** 1

<div class="post-metadata">

**Author:** ![sandeep87](https://avatars.discourse-cdn.com/v4/letter/s/e9a140/32.png) [@sandeep87](https://forum.cloverdx.com/u/sandeep87)\
**Post date:** [August 8, 2009, 12:00am UTC](https://forum.cloverdx.com/t/reformat-node/567/1 "2009-08-08T00:00:00Z")

</div>

i have a problem in REFORMAT Transformation  
The process is that i have to take the data from MYSQL database. need to transform it and again load it into MYSQL database.  
the EXTRACTING part is done… now i have to transform…  
which includes cleaning rules… which can be done by REFORMAT Transformation.

The transformation includes the replacing some field values by other like we replace gender field value male/female with 1/0…

so is there any way of doing this…??  
if yes please provide me a sample code…  
it is not necessary to take file can be from database… if it can be done with a text file data its fine…  
please help me its urgent…

---

<div class="post-metadata">

**Author:** ![avackova](https://avatars.discourse-cdn.com/v4/letter/a/65b543/32.png) [@avackova](https://forum.cloverdx.com/u/avackova)\
**Post date:** [August 10, 2009, 9:13am UTC](https://forum.cloverdx.com/t/reformat-node/567/2 "2009-08-10T09:13:19Z")

</div>

Hello, you can do this in many ways, eg. simple replacing of the value:

```auto
	$0.Field1 := iif(index_of($Field1,"female")>-1,0,1); 

```

or you can create lookup table with records:

- male, 1

- female,0

and use this lookup in your transformation:

```auto
	$0.Field2 := lookup(gender,$0.Field2).value;

```

How to write transformation in CTL see [CloverETL Transformation Language concept](http://wiki.cloveretl.org/doku.php?id=transf%5Flang%5Fsyntax), for description of all CTL functions see [Clover ETL Transformation Language functions](http://wiki.cloveretl.org/doku.php?id=transf%5Flang%5Ffunctions).

---

<div class="post-metadata">

**Author:** ![sandeep87](https://avatars.discourse-cdn.com/v4/letter/s/e9a140/32.png) [@sandeep87](https://forum.cloverdx.com/u/sandeep87)\
**Post date:** [August 10, 2009, 9:21am UTC](https://forum.cloverdx.com/t/reformat-node/567/3 "2009-08-10T09:21:11Z")

</div>

Thanks for responding… That was great…

---

<div class="post-metadata">

**Author:** ![pjxyz](https://avatars.discourse-cdn.com/v4/letter/p/7ab992/32.png) [@pjxyz](https://forum.cloverdx.com/u/pjxyz)\
**Post date:** [May 29, 2011, 4:23am UTC](https://forum.cloverdx.com/t/reformat-node/567/4 "2011-05-29T04:23:51Z")

</div>

Hi All,  
I am new to clover ETl. I have a xls file from which I have to read data and insert in mysql database. The excel file has datatype like number and date but mysql supports integer and datetime. How di I convert it before loading. I tried this

$0.OrderId=integer double2integer(number $0.OrderId);

where $0.OrderId represents i/o port with one of number datatype and other of integer. But it throws error of invalid transformation.

Can anybody suggest?

Thanks In advance

---

<div class="post-metadata">

**Author:** ![avackova](https://avatars.discourse-cdn.com/v4/letter/a/65b543/32.png) [@avackova](https://forum.cloverdx.com/u/avackova)\
**Post date:** [May 30, 2011, 8:54am UTC](https://forum.cloverdx.com/t/reformat-node/567/5 "2011-05-30T08:54:30Z")

</div>

Hello,  
if the numbers are integers in fact, try to load them with Integer data field. It should work. The same with dates; you probably even don’t need to set a format.

---

<div class="post-metadata">

**Author:** ![pjxyz](https://avatars.discourse-cdn.com/v4/letter/p/7ab992/32.png) [@pjxyz](https://forum.cloverdx.com/u/pjxyz)\
**Post date:** [May 31, 2011, 5:27am UTC](https://forum.cloverdx.com/t/reformat-node/567/6 "2011-05-31T05:27:43Z")

</div>

Thanks Agata Vackova for the help. It worked. 🙂

---

<div class="post-metadata">

**Author:** ![pjxyz](https://avatars.discourse-cdn.com/v4/letter/p/7ab992/32.png) [@pjxyz](https://forum.cloverdx.com/u/pjxyz)\
**Post date:** [June 5, 2011, 4:10pm UTC](https://forum.cloverdx.com/t/reformat-node/567/7 "2011-06-05T16:10:18Z")

</div>

How can I compare a Date port in reformat node when both input and output ports are of type date?

I want to add a filter condition. I tried PurchaseDate \> ‘1996-07-04’ and PurchaseDate \> 1996-07-04 but none worked.

Any suggestions??

Thanks in advance

---

<div class="post-metadata">

**Author:** ![avackova](https://avatars.discourse-cdn.com/v4/letter/a/65b543/32.png) [@avackova](https://forum.cloverdx.com/u/avackova)\
**Post date:** [June 6, 2011, 10:57am UTC](https://forum.cloverdx.com/t/reformat-node/567/8 "2011-06-06T10:57:36Z")

</div>

Hello,  
clover field name has always be preceded by $ character, so your condition should look as follows:

```auto
$PurchaseDate > 1996-07-04

```

where _PurchaseDate_ is name of input field.

---

<div class="post-metadata">

**Author:** ![pjxyz](https://avatars.discourse-cdn.com/v4/letter/p/7ab992/32.png) [@pjxyz](https://forum.cloverdx.com/u/pjxyz)\
**Post date:** [June 7, 2011, 6:10am UTC](https://forum.cloverdx.com/t/reformat-node/567/9 "2011-06-07T06:10:47Z")

</div>

Sorry that was a typo error I had tried with

$0.PurchaseDate \> 1996-07-04 and $0.PurchaseDate \> ‘1996-07-04’ but none worked.

One more question will this be used in extfilter transformer? Can we add conditions in reformat?

Since I am used to Informatica I am learning clover etl transformers one by one.

Thanks in advance.

Thanks,  
Purvi

---

<div class="post-metadata">

**Author:** ![avackova](https://avatars.discourse-cdn.com/v4/letter/a/65b543/32.png) [@avackova](https://forum.cloverdx.com/u/avackova)\
**Post date:** [June 7, 2011, 6:25am UTC](https://forum.cloverdx.com/t/reformat-node/567/10 "2011-06-07T06:25:10Z")

</div>

Hello Purvi,  
what do you mean by “doesn’t work”? Does it throw an error? Or doesn’t evaluate the expression properly? Please show your graph/node.  
Such expression should work in any component, that uses CTL, that means Reformat, ExtFilter, DataGenerator etc.  
If you would write your aim, I could give you a better concrete advice.

---

<div class="post-metadata">

**Author:** ![pjxyz](https://avatars.discourse-cdn.com/v4/letter/p/7ab992/32.png) [@pjxyz](https://forum.cloverdx.com/u/pjxyz)\
**Post date:** [June 8, 2011, 4:13am UTC](https://forum.cloverdx.com/t/reformat-node/567/11 "2011-06-08T04:13:14Z")

</div>

Hi Agata,

My aim is to filter all the rows greater than a specified date. I tried to add in the reformat port with following expression:

_$0.PurchaseDate \> 1996-07-04_  
It threw following error:  
Type mismatch: cannot convert from ‘boolean’ to ‘date’ at mapping 4, column 2

when I tried with  
_$0.PurchaseDate \> ‘1996-07-04’_  
It threw following error:  
Incompatible types ‘date’ and ‘string’ for binary operator at mapping 4, column 20

_When I tried in ExtFilter transformer it threw following error:_

ERROR [WatchDog] - Node EXT\_FILTER0 finished with status: ERROR caused by: Interpreter runtime exception on line 2 column 1 - compare: unsupported compare operation for null value  
ERROR [WatchDog] - Node EXT\_FILTER0 error details:  
org.jetel.ctl.TransformLangExecutorRuntimeException: Interpreter runtime exception on line 2 column 1 - compare: unsupported compare operation for null value

- [Demo1.grf](https://cdn.cloverdx.com/forum/13616%5F5d42a68d654abd967b414bce801da0ce/Demo1.grf)

---

<div class="post-metadata">

**Author:** ![avackova](https://avatars.discourse-cdn.com/v4/letter/a/65b543/32.png) [@avackova](https://forum.cloverdx.com/u/avackova)\
**Post date:** [June 8, 2011, 9:54am UTC](https://forum.cloverdx.com/t/reformat-node/567/12 "2011-06-08T09:54:09Z")

</div>

Hello Purvi,  
your graph is proper, but evidently a record with null (empty) _PurchaseDate_ comes to the filter. To avoid the error, you can add checking of the null values, so your filter expression should look as follows:

```auto
!isnull($0.Purchase_date) && $0.Purchase_date > 1997-02-02;

```

The same aim can be also achieved with Partition or Reformat components (see attached graphs).

- [Demo1.grf](https://cdn.cloverdx.com/forum/417%5F12fc56f653eadb4da26a029249be6dda/Demo1.grf)
- [Demo1Partition.grf](https://cdn.cloverdx.com/forum/417%5Fd7d7b8fe9fedffd34de6077d2055dcb7/Demo1Partition.grf)
- [Demo1Reformat.grf](https://cdn.cloverdx.com/forum/417%5Fc65a6ec093019580d352ae55da642f43/Demo1Reformat.grf)

---

<div class="post-metadata">

**Author:** ![pjxyz](https://avatars.discourse-cdn.com/v4/letter/p/7ab992/32.png) [@pjxyz](https://forum.cloverdx.com/u/pjxyz)\
**Post date:** [June 10, 2011, 5:24am UTC](https://forum.cloverdx.com/t/reformat-node/567/13 "2011-06-10T05:24:10Z")

</div>

Hi Agata,

Thanks for helping. 🙂 But earlier I thought same filter condition can work in reformat as well. Is that true?

Thanks,

Purvi

---

<div class="post-metadata">

**Author:** ![avackova](https://avatars.discourse-cdn.com/v4/letter/a/65b543/32.png) [@avackova](https://forum.cloverdx.com/u/avackova)\
**Post date:** [June 10, 2011, 6:34am UTC](https://forum.cloverdx.com/t/reformat-node/567/14 "2011-06-10T06:34:43Z")

</div>

If properly used:

```auto
function integer transform() {
	if (!isnull($0.Purchase_date) && $0.Purchase_date > 1997-02-02) {
		$0.* = $0.*; //map input to 0th output port
		return 0;
	}
	$1.* = $0.*;//map input to 1st input port
	return 1;
}

```

or:

```auto
function integer transform() {
	if (!isnull($0.Purchase_date) && $0.Purchase_date > 1997-02-02) {
		$0.* = $0.*;
		return 0;
	}
	return SKIP; //don't send record to output
}

```