Define Variable automatically

      Define Variable automatically

      Halo community,


      ist es möglich eine Variable im ETL-Prozess in Abhängigkeit des aktuellen

      Tages automatisch zu definieren?

      Die Variable soll demnach am 16.11.2014 wie folgt lauten "2014 KW 46"

      und am heutigen Tage den 18.11.2014 eben "2014 KW 47".

      Vielen Dank für die Unterstützung.

      mit freundlichen Grüßen

      Dnegelof

      Post was edited 1 time, last by “Dnegelof” ().

      I got the same question but I am struggling because I am not sure where to state the script and how to set the Variable.
      How does my code would have to look like?
      Would it be like this:
      The Variable I would like to set is called $Date

      import java.text.SimpleDateFormat;
      SimpleDateFormat outputFormat=new SimpleDateFormat("ww");
      Date today=new Date();

      $Date==println outputFormat.format(today);

      Thanky for your help in advance

      Kind regards
      Hi Laloune,

      Could you please elaborate a little bit more? Once I have the groovy job set, how do I update the value of my variable $Date? I need to use this variable to extract the data for the Current Date only, from a sql table of nearly 5mil rows transaction data.

      If not variable, how do I use this 'Date' value in my query for filtering out my extract data?

      Thanks and Regards,
      Kiran
      Hi Kiran,

      you can use the variable as a constant. For instance in you sql query you would have the following:

      SELECT
      *
      FROM
      myBIGTable
      WHERE
      theDate = '${Date}'

      with a groovy job you set the variable and execute the load using API.executeLoad('yourload');

      no?
      laloune

      Post hoc, non est propter hoc
      Here is what I did:

      1. Set up a variable by name Date - referred to as ${Date}

      2. Set up a groovy job as follows:

      Java Source Code

      1. import java.text.SimpleDateFormat;
      2. SimpleDateFormat outputFormat=new SimpleDateFormat("dd-MMM-yyyy");
      3. Date today=new Date();
      4. theDateValue=outputFormat.format(today);
      5. API.setProperty('${Date}',theDateValue);


      3. Created an extract with the query as you had mentioned.

      I did not create the load yet, but, when I preview the extract, I dont see the result I want. It does not seem to be filtering as per the date. Where am I going wrong?

      Regards,
      Kiran
      Hi Kiran,

      this variable stuff works this way:

      - you define a variable and a default value for it (click on the variable then fill the field)
      - in the groovy job the command API.setProperty('xxx'); will overwrite the default value and execute the load/job using the newly set property

      you can display the result of the extract from the job and using the set property. For that append your groovy job using the following:

      Source Code

      1. mySource=API.initSource('name_of_your_extract');
      2. while (mySource.nextRow())
      3. {
      4. interestingColumn1=mySource.getColumnString('interesting_column_name1');
      5. interestingColumn2=mySource.getColumnString('interesting_column_name2');
      6. LOG.info(interestingColumn1 + ' / ' + interestingColumn2);
      7. }


      more info here in the groovyjob sample

      hope this helps
      laloune

      Post hoc, non est propter hoc