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.............
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.............
-
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. -
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 -
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 -
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. -
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 -
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). -
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
}
Please sign in to leave a comment.
Comments 13