Customer Portal

Comments 13

  • Avatar
    avackova
    0
    Comment actions Permalink
    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.
  • Avatar
    sandeep87
    0
    Comment actions Permalink
    Thanks for responding....... That was great....
  • Avatar
    pjxyz
    0
    Comment actions Permalink
    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
  • Avatar
    avackova
    0
    Comment actions Permalink
    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.
  • Avatar
    pjxyz
    0
    Comment actions Permalink
    Thanks Agata Vackova for the help. It worked. :)
  • Avatar
    pjxyz
    0
    Comment actions Permalink
    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
  • Avatar
    avackova
    0
    Comment actions Permalink
    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.
  • Avatar
    pjxyz
    0
    Comment actions Permalink
    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
  • Avatar
    avackova
    0
    Comment actions Permalink
    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.
  • Avatar
    pjxyz
    0
    Comment actions Permalink
    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
  • Avatar
    avackova
    0
    Comment actions Permalink
    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).
  • Avatar
    pjxyz
    0
    Comment actions Permalink
    Hi Agata,

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

    Thanks,

    Purvi
  • Avatar
    avackova
    0
    Comment actions Permalink
    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.