maketime - script and chart function

Overview

Open the Data load editor and add the load script below to a new tab.

The load script contains:

• A dataset containing a set of transactions , which is loaded into a table called Transactions.

• Transaction times provided across three fields: hours, minutes, and seconds.

• The creation of a field, transaction_time, that returns the time in the format of the TimeFormat system variable.

Load script

SET TimeFormat='h:mm:ss TT';

Transactions:
    Load
        *,
        maketime(transaction_hour, transaction_minute, transaction_second) as transaction_time
        ; 
Load * Inline [
transaction_id, transaction_hour, transaction_minute, transaction_second, transaction_amount, transaction_quantity, customer_id
3750, 18, 43, 30, 12423.56, 23, 2038593
3751, 6, 32, 07, 5356.31, 6, 203521
3752, 12, 09, 16, 15.75, 1, 5646471
3753, 21, 43, 41, 7, 3036491
3754, 17, 55, 22, 21484.21, 1356, 049681
3756, 2, 52, 22, -59.18, 2, 2038593
3757, 9, 25, 23, 3177.4, 21, 203521
];

Results

Load the data and open a sheet. Create a new table and add these fields as dimensions:

• transaction_hour

• transaction_minute

• transaction_second

• transaction_time

The transaction_time field is created in the preceding load statement by using the maketime() function, and passing the hour, minute, and second fields as function arguments.

The function then combines and converts these values into a time field, returning the results in the time format of the TimeFormat system variable.

Overview

Open the Data load editor and add the load script below to a new tab.

The load script contains:

• The same dataset and scenario as the first example.

• The creation of a field, transaction_time, which will allow us to show the results in 24-hour time format without modifying the TimeFormat system variable.

Load script

SET TimeFormat='h:mm:ss TT';

Transactions:
    Load
        *,
        time(maketime(transaction_hour, transaction_minute, transaction_second),'h:mm:ss') as transaction_time
        ; 
Load * Inline [
transaction_id, transaction_hour, transaction_minute, transaction_second, transaction_amount, transaction_quantity, customer_id
3750, 18, 43, 30, 12423.56, 23, 2038593
3751, 6, 32, 07, 5356.31, 6, 203521
3752, 12, 09, 16, 15.75, 1, 5646471
3753, 21, 43, 41, 7, 3036491
3754, 17, 55, 22, 21484.21, 1356, 049681
3756, 2, 52, 22, -59.18, 2, 2038593
3757, 9, 25, 23, 3177.4, 21, 203521
];

Results

Load the data and open a sheet. Create a new table and add these fields as dimensions:

• transaction_hour

• transaction_minute

• transaction_second

• transaction_time

In this instance, the maketime() function is nested inside the time() function. The second argument of the time() function sets the format of the maketime() function results as the required h:mm:ss.

Overview

Open the Data load editor and add the load script below to a new tab.

The load script contains:

• A dataset containing a set of transactions , which is loaded into a table called Transactions.

• Transaction times provided across two fields: hours and minutes.

• The creation of a field, transaction_time, that returns the time in the format of the TimeFormat system variable.

Create a chart object measure transaction_time, that returns a time in the format h:mm:ss TT.

Load script

SET TimeFormat='h:mm:ss TT';

Transactions:
Load * Inline [
transaction_id, transaction_hour, transaction_minute, transaction_amount, transaction_quantity, customer_id
3750, 18, 43, 12423.56, 23, 2038593
3751, 6, 32, 5356.31, 6, 203521
3752, 12, 09, 15.75, 1, 5646471
3753, 21, 43, 7, 3036491
3754, 17, 55, 21484.21, 1356, 049681
3756, 2, 52, -59.18, 2, 2038593
3757, 9, 25, 3177.4, 21, 203521
];

Results

Load the data and open a sheet. Create a new table and add these fields as dimensions:

• transaction_hour

• transaction_minute

To calculate the transaction_time, create this measure:

=maketime(transaction_hour,transaction_minute)

The transaction_time measure is created in the chart object by using the maketime() function, and passing the hour and minute fields as function arguments.

The function then combines these values, and seconds are assumed to be 00. These values are then converted into a time field, returning the results in the format of the TimeFormat system variable.

Overview

Create a calendar dataset for the month of January 2022, broken out into eight-hour increments.

Load script

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

tmpCalendar:
	load 
		*
    where year(date)=2022;
load
	date(recno()+makedate(2021,12,31)) as date
AutoGenerate 31;

Left join(tmpCalendar)
load
	maketime((recno()-1)*8,00,00) as time
autogenerate 3;

Calendar:
load
	timestamp(date + time) as timestamp
resident tmpCalendar;

drop table tmpCalendar;

Results

The initial autogenerate function creates a calendar containing all the dates in January in a table called tmpCalendar.

A second table, containing three records, is created. For each record, recno() – 1 is taken (values 0, 1, 2) and the result is multiplied by 8. As a result, this generates the values 0, 8 16. These values are used as the hour parameter in a maketime() function, with minute and second values of 0. As a result, the table contains three time fields: 12:00:00 AM, 8:00:00 AM, and 4:00:00 PM.

This table is joined to the tmpCalendar table. Because there are no matching fields between the two tables for the join, the time rows are added to each date row. As a result, each date row is now repeated three times with each time value.

Finally, the Calendar table is created from a resident load of the tmpCalendar table. The date and time fields are concatenated and wrapped in the timestamp() function to create the timestamp field.

The tmpCalendar table is then dropped.