Orchestrating the data on time-based characteristics is an important task while you are analyzing the progress of your work. While we are dealing with the time characteristics, the Data Time Tool will be handy to deal with different date formats.
Let say we have two data sets which have a daily sales order data in every store and a monthly target to achieve in each store. We need a daily report which will have the target that needs to be met in the remaining days of the month.
First, we need to generate a common field based on the date(In this case month field in the same format) in both tables.
After creating month fields in the same format, we can join the tables with the join tool using the below configuration.
After joining the tables, we need to use the running total tool to find the amount we are receiving at the end of each sales order as below.
After this, we can use the sort tool to sort the data based on order date. We can use the formula tool to calculate the current target after each sales order as shown below in the workflow.
The select tool is used to remove the unwanted fields in the output data file, in this case, we have removed the calculated field, running total, from the output and generate the output as below.
Using this similar method, we can combine various data sets with different date formats and create structured data for reporting.