I created a two jobs. An orchestration job and a transformation job. The orchestration job runs the transformation job with a loop iterator attached it to. The transformation job is simple and I am looking to filter on all rows of data that have a date <= ${year_iterator}. I set the starting date for the variable and how many years I would like it to run. This worked without any issue.
I have a second set up with a more complicated transformation job. I need to filter on date <= ${year_iterator}-12-31, and date <${year_iterator}-01-01.
I used the two underlined filters in the job and I do not get any errors, but I return no data in those components. Is there a way to fix the month and date and pair that with the ${year_iterator} variable?
Thank you.
Maybe try creating the filter values in a variable from a separate job component? Often I will do this in a Query Result to Scalar component to set the filter values in a job variable. Like 'start_date' and 'end_date'.
Then your filter just becomes date > ${start_date} and date <= ${end_date}...
Also check your comparison - the example above has date < ${year_iterator}-01-01. Did you really mean "less than" for the start date? :)
Cheers
It has got nothing to do with Matillion. Make sure you are type casting the variables accordingly. Looking at your query, it seems like you are using them as string concatenation. Once you typecast the value as date (according to your db), you should be able to get the data.
Thank you for your responses.
In one job, I am able to accomplishing the following.
I need all of the values up until a date less than the first day of the year (beginning balance). I also need all of the values less than or equal to the last day of the year (ending balance). I also need all of the values that fall between the first day of the year and the last day of the year (activity during period). I can accomplish this by doing the following in three different filter components:
Beginning Balance: Year < ${year_iterator}
Ending Balance: year <= ${year_iterator}
Activity during Period: year = ${year_iterator}
I am able to run an orchestration job with a loop iterator that has a starting year and an ending year. The output of this is a view for each year that I iterate through.
I also need to accomplish the same thing but by quarter. A similar logic does not seem like it will work for me. I do not see how I can accomplish the same with a year filter and a quarter filter. I can filter on year, but if I want everything prior to 2023 Q2 and do year < 2023, and Quarter <2, then I will miss out on quarters 3 and 4 in prior years.