Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Published by Scroll Versions from space DEV and version next

...

FlightNumberGateArrival
123412/15/17 11:35
21222/15/17 11:58
51032/15/17 11:21
840142/15/17 12:08
9952/16/17 12:12
11662/16/17 13:32
87672/15/17 16:43
949482/15/17 21:00
10292/14/17 19:21
77102/16/17 12:31

TransformTransformation:

You are interested in generating a status report on today's flights. To assist, you must generate columns with the current date and time values:

Tip

Tip: You should create separate columns containing static values for NOW and TODAY functions. Avoid creating multiple instances of each function in your dataset, as the values calculated in them can vary at execution time.

d-

...

trans
RawWrangletrue
p03Value'currentTime'
Typestep
WrangleTextderive type:single value: NOW('America\/Los_Angeles') as: 'currentTime'
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02ValueNOW('America\/Los_Angeles')
p03NameNew column name

...

SearchTermNew formula

D trans
RawWrangletrue
p03Value'currentDate'
Typestep
WrangleTextderive type:single value: TODAY('America\/Los_Angeles') as: 'currentDate'
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02ValueTODAY('America\/Los_Angeles')
p03NameNew column name
SearchTermNew formula

Next, you want to identify the flights that are landing today. In this case, you can use the DATEDIF function to determine if the Arrival value matches the currentTime value within one day:

Info

NOTE: The DATEDIF function computes difference based on the difference from the first date to the second date based on the unit of measure. So, a timestamp that is 23 hours difference from the base timestamp can be within the same unit of day, even though the dates may be different (2/15/2017 vs. 2/14/2017).

d-

...

trans
RawWrangletrue
p03Value'today'
Typestep
WrangleTextderive type:single value: datedif(currentDate, Arrival, day) as: 'today'
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02ValueDATEDIF(currentDate, Arrival, day)
p03NameNew column name
SearchTermNew formula

Since you are focusing on today only, you can remove all of the rows that do not apply to today:

d-

...

trans
RawWrangletrue
p03Valuetoday <> 0
Typestep
WrangleTextdelete row: today <> 0
p01NameCondition
p01ValueCustom formula
p02NameType of formula
p02ValueCustom single
p03NameCondition
p04ValueDelete matching rows
p04NameAction
SearchTermFilter rows

Now focusing on today's dates, you can calculate the difference between the current time and the arrival time by the minute:

d-

...

trans
RawWrangletrue
p03Value'status'
Typestep
WrangleTextderive type:single value: datedif(currentTime, Arrival, minute) as: 'status'
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02ValueDATEDIF(currentTime, Arrival, minute)
p03NameNew column name
SearchTermNew formula

Using the numeric values in the status column, you can compose the following transform, which identifies status of each flight:

d-

...

trans
RawWrangletrue
Typestep
WrangleTextset col: status value: if(status < -20, 'arrived', if(status > 20, 'scheduled', if(status <= 0, 'landed', 'arriving')))
p01NameColumns
p01Valuestatus
p02NameFormula
p02Valueif(status < -20, 'arrived', if(status > 20, 'scheduled', if(status <= 0, 'landed', 'arriving')))
SearchTermEdit column with formula

Results:

You now have a daily flight status report:

...