Compose
Compose a datetime value from year, month, and day values.
If the table does not yet have any datetime fields in it, begin by using Edit - Schema to create a datetime field, and then use that field with the Compose template.
Launch the template by choosing a datetime field and then double-clicking the Compose template. When the template launches we can specify options.
Example
We start with a table with a single field of type datetime, called Date. We have used the Layers pane to hide the mfd_id field, for a simpler illustration.
With the focus on the table window, in the Transform pane we choose the Date field and then we double-click the Compose template to launch it.
In the Compose template, we enter 2020 for the Year, 11 for the Month and 26 for the Day. If we prefer, we can choose any numeric field in the table from the pull down menus for the year, month, or day boxes, or we could choose Expression in the boxes to have the year, month, or day computed from an SQL expression.
For the Result destination, we choose New Field and then enter Date only as the name of the new field to add to the table. We could use whatever name we want, but it is wise to use names that remind us of what they are supposed to be. We leave the Result type at the default datetime.
If we preferred, we could have chosen some other existing datetime field in the table as the Result destination, or we could have chosen same field to save the result into the same Date field if we would like to update it "in place."
Press Transform.
The template creates a new datetime type field called Composed date and populates it with a datetime value, the same one for all records, composed from the Year, Month, and Day values we specified. The time portion of the composed date is set to 0:00:00.
The result is a table with 0:00:00 for the time parts and November 26, 2020, for the date part.
Using Fields and Expressions
We can compose dates using fields and expressions to populate the Year, Month, and Day values, to create different dates for each record.
We start again with a table with a single field of type datetime, called Date.
With the focus on the table window, in the Transform pane we choose the Date field and then we double-click the Compose template to launch it.
In the Compose template, we choose Expression for the Year, and then enter the following expression into the Expression dialog:
(TABLE CALL ValueSequenceRandomInt(1, 21, [mfd_id]))
+ 2000
The expression generates a random number from 2000 to 2020, inclusive, to use as the Year value. The mfd_id field is used as a seed for the pseudorandom generator function. Press OK in the Expression dialog to get back to the Transform pane.
We also choose Expression for the Month and then enter the following expression:
(TABLE CALL ValueSequenceRandomInt(1, 12,
DateTimeSecond([Date]))) + 1
The expression generates a random number from 1 to 12, inclusive, to use as the Month value. To use a different seed for the pseudorandom generator function, instead of using whatever value is in the mfd_id field as the seed for each record, we extract the seconds part of the Date field for each record.
Press OK in the Expression dialog to get back to the Transform pane.
In the Day box we choose the mfd_id field from the pull down menu. That will use whatever is the value in the mfd_id field as the day. Our table only has a few records and we know that the value of the mfd_id field in all of them is less than 30, so we feel safe doing that for an example.
For the Result destination, we choose New Field and then enter Composed date as the name of the new field to add to the table. We leave the Result type at the default datetime.
Press Transform.
The template creates a new datetime type field called Composed date and populates it with datetime values, where the year of each date is a random number from 2000 to 2020, the month is a random number from 1 to 12, and the day number is the same as the mfd_id value for each record.
In such a small table where we know all mfd_id values are less than 30 we might feel safe use the mfd_id field to populate the day number, but that is not a good idea for bigger tables. If any mfd_id value is above 28, 29, 30, or 31, depending on what month is implied by the month and year values, and thus would result in an impossible date, the composed date would be NULL.
We can re-run the transform using an expression to generate the day number.
In the Compose template, we use the same Expression for the Year:
(TABLE CALL ValueSequenceRandomInt(1, 21, [mfd_id]))
+ 2000
For the Month, we choose Value and enter 12, just to show that we can mix use of fields, expressions and fixed values. This will cause all composed dates to be in the month of December.
In the Day parameter we choose Expression and then enter the following expression:
(TABLE CALL ValueSequenceRandomInt(1, 31,
DateTimeMinute([Date]))) + 1
The expression generates a random number from 1 to 31, inclusive, to use as the Day value. To use a different seed for the pseudorandom generator function, we extract the minutes part of the Date field for each record. Limiting days to a range of 1 to 31 is being lazy, since it works in this particular example given the month we have specified is December, which has 31 days. A pseudorandom expression for a day number that would work for all months would be more complex, since it would have to automatically limit to 31, 30, 29, or 28 days depending on the month and whether (for February) if the year was a leap year or not.
Press OK in the Expression dialog to get back to the Transform pane.
For the Result destination, from the pull down menu we choose the Composed date field we created in the prior example. We leave the Result type at the default datetime.
Press Transform.
The Composed date field is updated with new dates, each being a day in the month of December, with years picked at random from 2000 to 2020 and days picked at random from 1 to 31.