Reformat node

CloverDX Platform
1

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…

2

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

	$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:

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

How to write transformation in CTL see CloverETL Transformation Language concept, for description of all CTL functions see Clover ETL Transformation Language functions.

3

Thanks for responding… That was great…

4

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

5

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.

6

Thanks Agata Vackova for the help. It worked. :slight_smile:

7

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

8

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

$PurchaseDate > 1996-07-04

where PurchaseDate is name of input field.

9

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

10

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.

11

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

12

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:

!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).

13

Hi Agata,

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

Thanks,

Purvi

14

If properly used:

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:

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
}