Using inline loads to load data

This example demonstrates how you can use back ticks to enclose the inline data. The data in the example contains square bracket characters, which makes square brackets incompatible as enclosures for the LOAD statement.

Overview

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

Load script

DataTable:

Load * inline 
`
custom_property, date, amount
[11402], 1/1/2024, 92.99
[11403], 1/2/2024, 102.34
[11404], 1/3/2024, 50.26
[11405], 1/4/2024, 153.69
[11406], 1/5/2024, 75.62
`;

Results

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

• custom_property

• date

• amount

Overview

Exchanging the delimiter is useful when data in the table contains commas, which would otherwise signal the switch to the next column in the table.

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

The load script contains:

• Four tables to be loaded. They are as follows:

  • Table1: Delimiter is a vertical bar (pipe). The data in the Phrase column contains commas.

  • Table2: Delimiter is a colon.

  • Table3: Delimiter is a back slash.

  • Table4: The table is delimited by pressing the Tab key on the keyboard. This option is useful when you are copying a table from a word processor and pasting it into the script.

  Table1 shows a practical use case for using an alternative delimiter. The other tables show more delimiter options.

Load script

Table1:
LOAD * INLINE [
ID|Phrase
1 | 'The transaction was complete, but the order has not yet shipped.'
2 | 'We need to confirm the following details: sales rep number, order status, and shipping priority.'
] (Delimiter is '|');

Table2:
LOAD * INLINE [
Num1:Chr1
1:A
2:B ] (Delimiter is ':');

Table3:
LOAD * INLINE [
Num2\Chr2
1\A
2\B ] (Delimiter is \\);

Table4:
LOAD * INLINE [
Num3	Chr3
1	A
2	B ] (Delimiter is '\t');

Results

Load the data and open a sheet. Create four tables and add the dimensions to them as follows:

• Table1: ID, Phrase

• Table2: Num1, Chr1

• Table3: Num2, Chr2

• Table4: Num3, Chr3

Overview

The following script first loads a series of fields from a connector, then loads an inline table with a common field and an additional field. This new field provides another property about the row item that was not loaded through the data connection. Note that this only works smoothly when there is a single field name in common between the two tables. If there is more than one common field name, a synthetic key is produced.

In this truncated example, we load a table of order details from a data connection. Then, we use an inline table to add an additional field, Supplemental Info. This could, for example, contain details about special comments about specific order IDs.

Load script

SET DateFormat='MM/DD/YYYY';

Orders:

LIB CONNECT TO 'My_Generic_Connection';

LOAD PRODUCT_DIVISION_A_ORDER_ID as Order ID, 
PRODUCT_DIVISION_A_ORDER_DATE as ISSUE_KEY as Order Date, 
PRODUCT_DIVISION_A_TYPE as Product Type, 
PRODUCT_DIVISION_A_SALES_MANAGER as Manager,
PRODUCT_DIVISION_A_SHIPPED_STATUS as Shipped Status;

SELECT PRODUCT_DIVISION_A_ORDER_ID,
PRODUCT_DIVISION_A_ORDER_DATE,
PRODUCT_DIVISION_A_TYPE,
PRODUCT_DIVISION_A_SALES_MANAGER,
PRODUCT_DIVISION_A_SHIPPED_STATUS

FROM SourceTable
WITH PROPERTIES (

[...]

);

Orders_Inline_Info:
load * inline [
Order ID,Supplemental Info
PSF-001014,'Bulk order, pending deal with Paracel.'
PSF-001625,'NOTE: Product damaged. Investigation required.'
];

Results

After loading the data, let's say we add the following dimensions to a table:

• Order ID

• Order Date

• Product Type

• Manager

• Shipped Status

• Supplemental Info

The resulting chart could look like this.

Note how all fields from both tables can be added to the same visualization. The tables are associated to each other in the data model.

Overview

This example demonstrates how you can load an inline table without defining column names.

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

Load script

Expenses:

Load * inline
[
1,Category1,40566.49
2,Category2,3504.54
3,Category3,593206.30
] (no labels);

Results

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

• @1

• @2

• @3

Overview

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

The load script contains:

• An inline load statement defining the table DataTable.
• The use of fix (as part of the Table format specification) to indicate that the data to load is in fixed record format.

Load script

DataTable:
LOAD
"@1:4",
"@5:8",
"@9:12",
"@13:16"
INLINE [
a   b   c   d
1   2   3   4
x   y   z   w
] (fix, utf8, embedded labels);

Results

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

• a

• b

• c

• d

Overview

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

The load script contains:

• A simple inline load to define the table T1.

• Use of the Quotes specifier (with the value msq) to support multi-line content.

• Length values specified in feet and inches.

• The double quotation mark symbol (") indicates the end of a multi-line entry. In this example, the same symbol is also used to denote the inch values. This is done to show the syntax and resulting behavior if you need to use both in the same LOAD statement. Essentially, entering the " symbol for two consecutive characters ("") indicates the code is to be interpreted as a single text instance of the symbol.

   

Load script

T1:
Load *, recno() as ID inline [
Length
"1' 2""
22' 10"""
14' 8"  ] (msq);

Results

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

• ID

• Length

To view multi-line content as intended, The Wrap text in cells setting must be turned on in the properties panel. Additionally, the app theme (or visualization settings) must allow for the sufficient Row height (in lines).

In the above table, the record with an ID value of 1 contains multi-line content.

Overview

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

Load script

ORDERS:

Load Original as Updated,
* Inline
[
Original
ProductA
ProductB
ProductC
];

Results

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

• Original

• Updated

Overview

This example shows how you can define a calculated field to indicate the ID of each data row, without manually typing each ID value.

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

Load script

ORDERS:

Load RowNo() as ID,
* Inline
[
ProductName
A
B
C
D
E
];

Results

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

• ID

• ProductName

Overview

This example shows several ways in which a simple transformation can be applied to inline data. You can use one or multiple LOAD statements, and configure the syntax in different ways, to define whether or not a transformation replaces the original field or if both original and new fields are loaded.

In particular, note the variations in the following and how they affect the output:

• Number of LOAD statements within the table.

• Presence or absence of * symbol (loads all subsequent fields).

• Presence or absence of commas (, symbol).

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

The load script contains:

• Four tables, all of which reference the same data. The field names are different.

  • DerivedYears1: Uses two LOAD statements to load an inline column and a column containing a transformation of the inline column.

  • DerivedYears2: Uses two LOAD statements. The script loads an inline column, then loads a calculated field transformed from the original column. With the second LOAD statement, the original column is not loaded into the table, effectively making the new field a replacement of the original field.

  • DerivedYears3: Uses a single LOAD statement to load both the original inline column and a transformed column.

  • DerivedYears4: Uses a single LOAD statement to define an inline field and a transformation of that original field. Only the transformed field is loaded into the table.

Load script

DerivedYears1:

Load *,
Year(initial_date1) as derived_year1
;
    
Load
* inline [
initial_date1
'1/1/2022'
'1/1/2023'
'1/1/2024'
];

DerivedYears2:

Load
Year(initial_date2) as derived_year2
;
    
Load
* inline [
initial_date2
'1/1/2022'
'1/1/2023'
'1/1/2024'
];

DerivedYears3:

Load Year(initial_date3) as derived_year3,
* inline [
initial_date3
'1/1/2022'
'1/1/2023'
'1/1/2024'
];

DerivedYears4:

Load Year(initial_date4) as derived_year4
inline [
initial_date4
'1/1/2022'
'1/1/2023'
'1/1/2024'
];

Results

Load the data and open a sheet. Create three tables and add the dimensions to them as follows:

• DerivedYears1: initial_date1, derived_year1

• DerivedYears2: initial_date2

• DerivedYears3: initial_date3, derived_year3

Overview

Suppose you are pasting information from a large text file into the script editor, but want to perform additional processing on the records in the dataset. For example, you want to remove certain characters and prefixes, and capitalize each word.

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

The load script contains:

• An inline table, Transformations, in which a transformation is defined to change text content of a field. The text being transformed is added after this.

• Two LOAD statements. With the configuration in the example, only the derived field ProductName_Trimmed is loaded into the table.

Load script

Transformations:

Load 
Capitalize(TextBetween(OrigColumn, '{','}')) as ProductName_Trimmed;

Load * inline [
OrigColumn
Product: {soft drinks and other beverages}
Product:  {snack food}
Product: {electronics and video games}
];

Results

Load the data and open a sheet. Create a new table and add this field as a dimension: ProductName_Trimmed.

Overview

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

The load script contains:

• A simple inline load to define the table Purchases.

• The insertion of a calculated field, OrderSize. This field is a transformation of the amount field from the inline dataset. It categorizes purchases as small or large, depending on whether or not the amount surpasses $2000.00.

• A where clause that prevent records from being loaded when the amount is less than $0.05.

Load script

Purchases:

Load
If(amount<2000,'Small Purchase','Large Purchase') as OrderSize,

* inline
[
ID, amount
1,2660.39
2,100.01
3,500.42
4,5023.99
5,0.04
6,2002.39
7,31034.53
8,1643.58
9,3993.65
10,614.34
11,675.93
]
WHERE
amount>0.05
;

Results

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

• ID

• amount

• OrderSize

In the above table, the transaction with an ID of 5 was not loaded into the table. This is because the amount value is less than $0.05.

Overview

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

The load script contains:

• A simple inline load to define the table T2.

• The NullInterpret variable defined as two single quotation marks. When the script is loaded, records containing this value are processed as null values.

Load script

set nullinterpret = '';
T2:
Load * inline [
Test
''
x ];

Results

Load the data and open a sheet. Create a new table and add this field as a dimension: Test.

In the above table, the first record is a null value. Therefore, it is not included in the visualization. Null values existing in tables with additional dimensions would be denoted with a - symbol.