How to change dynamically value field of "Generate rows step" in Pentaho data integration, to be used to get data from A rest API

Question

I want to access data from a rest API using pentaho data integration and save the result into a table.

I am using the following steps:

1. Generate rows step
2. Rest Client
3. JSON input
4. select values
5. Table output

The API I want to access take a date as an argument.

My Question is here:

How can I automatically change date in the url (generate row step) so that I can get data of many dates at a time. With one date the transformation works fine.

Note that the first part of the url was intentionally changed. Now I want to dynamically change that dates.

Answer 1

You can't do that in the Generate Rows step, you'll have to calculate dates and concatenate them in the URL.

Step 1 - Generate rows with how many days backwards you want (say 60)

Step 2 - Add Sequence step - Beggining value -1, increment of -1.

Step 3 - Formula step - create a column with desired name, formula = TODAY(), data type DATE.

Step 4 - Calculator Step - create a new column that uses the calculation "DATE A + B Days", use Field A = Date from formula step, Field B = 'valuename'(Default field name from Add Sequence step). Data type DATE, conversion mask aaaa-MM-dd.

Using these 4 steps you now have the dates of 60 days backwards in the aaaa-MM-dd format, which you just have to concatenate to the end of your url, and run this url once for each date.