Apply rounding logic to Time data

Scenario:

We have flight data that has Flight Time upto Minute level. We also have weather data that has hourly weather parameters.

Requirement:

We need weather data for flight time with most nearer slots from weather data. For eg; if the flight time is between "1.30 - 2.30" it should come under 2'o clk slot.

Method 1

Step:1 We need to separate the hours from the date field from both the datasets. 

Considering the Weather dataset;

1.To Copy the Date column,

  Right click on the Date column and Copy -> Column. ‘HH’ column will be generated.

2. Right click on the new Date column (HH) -> Transform -> More

3. Choose hour(date) from transform operation, -> Select the date field in the Argument and click OK.

Step:2  Since flight dataset has zero in the time part, we need to make the time part zero in the weather dataset too for joining.

1. Right click on the Date -> Transform -> Make timepart zero

Step:3 In flight dataset, let us consider Dep_Time to extract the hours. By default, it has hours and minutes combined data. So we need to split them to get the departure hour. 

1. Right click on the DepTime and Split

2. Split by length as 2 from Right and click OK

Change the 24 hour to 0 

3. Right click -> Unique values -> Edit 24 to 0 and click OK.

Step:4 Here (Flight dataset) we need to convert DEP_TIME_MINS to hours to append with DEP_TIME_HOUR. 

1.Right click on DEP_TIME_MIN -> Transform .

2.Choose divide() as Operation and divide by value 60 -> click OK

3.Right click on the DEP_TIME_MIN  again -> Transform

4.Choose the plus() Operation and add with DEP_TIME_HH.

5.Right click on DEP_TIME_MIN  - > Transform -> Datatype (This is to convert the datatype to integer)

6.Choose INT as datatype and click OK

7.Right click on DEP_TIME_HH -> Transform to round the DEP_TIME_HH column. 

8.Choose round() as Operation and click OK

Step:5 In Flight Dataset, need to edit the column in such a way that if the hour is 24, the date should be changed to the next day and change 24 to 0 as weather data has 0 value for the next day morning data.

1.Right click on the DEP_TIME_HH and Add column (FinalDate)

2.Give expression as “ifCase(DEP_TIME_HH == “24”, dateAdd(“d”, 1, FL_DATE), FL_DATE)”

3.Right click on the DEP_TIME_HH -> Unique values

4.Replace 24 to 0 and click OK

Step:6   JOIN with Weather dataset

1.Click Add Dataset and choose Weather dataset

2.Click JOIN (Equi JOIN) and choose columns as FinalDate = Date, DEP_TIME_HH = HH, DEST = SiteId and click OK.

The date will be displayed as per the hour slots.