...
| FlightNumber | Gate | Arrival |
|---|
| 1234 | 1 | 2/15/17 11:35 |
| 212 | 2 | 2/15/17 11:58 |
| 510 | 3 | 2/15/17 11:21 |
| 8401 | 4 | 2/15/17 12:08 |
| 99 | 5 | 2/16/17 12:12 |
| 116 | 6 | 2/16/17 13:32 |
| 876 | 7 | 2/15/17 16:43 |
| 9494 | 8 | 2/15/17 21:00 |
| 102 | 9 | 2/14/17 19:21 |
| 77 | 10 | 2/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. |
...
| trans |
|---|
| RawWrangle | true |
|---|
| p03Value | 'currentTime' |
|---|
| Type | step |
|---|
| WrangleText | derive type:single value: NOW('America\/Los_Angeles') as: 'currentTime' |
|---|
| p01Name | Formula type |
|---|
| p01Value | Single row formula |
|---|
| p02Name | Formula |
|---|
| p02Value | NOW('America\/Los_Angeles') |
|---|
| p03Name | New column name |
|---|
|
...
| D trans |
|---|
| RawWrangle | true |
|---|
| p03Value | 'currentDate' |
|---|
| Type | step |
|---|
| WrangleText | derive type:single value: TODAY('America\/Los_Angeles') as: 'currentDate' |
|---|
| p01Name | Formula type |
|---|
| p01Value | Single row formula |
|---|
| p02Name | Formula |
|---|
| p02Value | TODAY('America\/Los_Angeles') |
|---|
| p03Name | New column name |
|---|
| SearchTerm | New 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). |
...
| trans |
|---|
| RawWrangle | true |
|---|
| p03Value | 'today' |
|---|
| Type | step |
|---|
| WrangleText | derive type:single value: datedif(currentDate, Arrival, day) as: 'today' |
|---|
| p01Name | Formula type |
|---|
| p01Value | Single row formula |
|---|
| p02Name | Formula |
|---|
| p02Value | DATEDIF(currentDate, Arrival, day) |
|---|
| p03Name | New column name |
|---|
| SearchTerm | New formula |
|---|
|
Since you are focusing on today only, you can remove all of the rows that do not apply to today:
...
| trans |
|---|
| RawWrangle | true |
|---|
| p03Value | today <> 0 |
|---|
| Type | step |
|---|
| WrangleText | delete row: today <> 0 |
|---|
| p01Name | Condition |
|---|
| p01Value | Custom formula |
|---|
| p02Name | Type of formula |
|---|
| p02Value | Custom single |
|---|
| p03Name | Condition |
|---|
| p04Value | Delete matching rows |
|---|
| p04Name | Action |
|---|
| SearchTerm | Filter rows |
|---|
|
Now focusing on today's dates, you can calculate the difference between the current time and the arrival time by the minute:
...
| trans |
|---|
| RawWrangle | true |
|---|
| p03Value | 'status' |
|---|
| Type | step |
|---|
| WrangleText | derive type:single value: datedif(currentTime, Arrival, minute) as: 'status' |
|---|
| p01Name | Formula type |
|---|
| p01Value | Single row formula |
|---|
| p02Name | Formula |
|---|
| p02Value | DATEDIF(currentTime, Arrival, minute) |
|---|
| p03Name | New column name |
|---|
| SearchTerm | New formula |
|---|
|
Using the numeric values in the status column, you can compose the following transform, which identifies status of each flight:
...
| trans |
|---|
| RawWrangle | true |
|---|
| Type | step |
|---|
| WrangleText | set col: status value: if(status < -20, 'arrived', if(status > 20, 'scheduled', if(status <= 0, 'landed', 'arriving'))) |
|---|
| p01Name | Columns |
|---|
| p01Value | status |
|---|
| p02Name | Formula |
|---|
| p02Value | if(status < -20, 'arrived', if(status > 20, 'scheduled', if(status <= 0, 'landed', 'arriving'))) |
|---|
| SearchTerm | Edit column with formula |
|---|
|
Results:
You now have a daily flight status report:
...
