Date Generation Transform by using different Date Functions

Description:

      Date_Generation transform is used for creating time dimension tables. This transform generates a column which holds the date values based on the start & end dates provided as an input to the transform by considering the increment provided to it.

Here is the step by step guide to achieve the transformation.

Create a Project

Create a Batch Job

Take Work Flow and Data Flow

Take Date Generation Transformation

Double Click on Date_Generation Transform and Provide the details as below

Take the Query Transformation and join to the Date Generation Transformation

Go to Query Transform and Create New Output Column

Provide the Name for the Output Column to return the week in year

Go to Functions and select Date Functions then week_in_year function and click on Next

Select the Date_Generation Transform generated column then click on Finish

After mapping the week_in_year and create another New Output Column to Quarter of the Year

Give the name and data type for another output column then click on OK

Go to Functions and select Date Functions and the select quarter and click on Next

Select the Date_Generation transform generated column and click on OK

Click on Finish

Again create another output column return last date

Name the column and data type for the newly created output column

Go to Date functions and select last_date function then click on Next

Click on Finish

After mapping the all new ouput columns ,Validate the current window

Take the Template Table and join to the Query Transformation

Execute the Job

After executing the job check the data in the Target

Thanks for following.