if - script and chart function

Overview

If can be used in a load script with other methods and objects, including variables. This example uses the threshold variable and includes a new field in the data model that is based on the threshold.

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

The load script contains:

• A dataset which is loaded into two data tables called:

  • Transactions: Includes transaction fields

  • Transaction_Buckets: Includes a transaction_id field and creates a new field, [Compared to Threshold], using the if function to compare the transaction_amount field to the threshold variable setting.

• A threshold variable statement set to 100.

Load script

Transactions:
Load * Inline [
transaction_id, transaction_date, transaction_amount, transaction_quantity, customer_id, size, color_code
3750, 20180830, 23.56, 2, 2038593, L, Red
3751, 20180907, 556.31, 6, 203521, m, orange
3752, 20180916, 5.75, 1, 5646471, S, blue
3753, 20180922, 125.00, 7, 3036491, l, Black
3754, 20180922, 484.21, 13, 049681, xs, Red
3756, 20180922, 59.18, 2, 2038593, M, Blue
3757, 20180923, 177.42, 21, 203521, XL, Black
];
 
set threshold = 100;
					
/* Create new table called Transaction_Buckets 
Compare transaction_amount field from Transaction table to threshold of 100.
Output results into a new field called Compared to Threshold
 */

Transaction_Buckets:
Load
   transaction_id,
   If(transaction_amount > $(threshold),'Greater than $(threshold)','Less than $(threshold)') as [Compared to Threshold]
Resident Transactions;

Results

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

• transaction_id

• [Compared to Threshold]

Overview

A data set contains product data. You want to identify and categorize products according to whether they are consumable or not.

Load script

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

The load script contains:

• A dataset which is loaded into a data table called Example.

• The following fields in the data table:

  • ProductID

  • ProductName

  • ProductCategory

Example:
LOAD * INLINE [
ProductID, ProductName, ProductCategory
1, "Laptop", "Electronics"
2, "Banana", "Fruits"
3, "Shampoo", "Toiletries"
4, "TV", "Electronics"
5, "Apple", "Fruits"
];

Results

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

• ProductID

• ProductName

• ProductCategory

Create the following measure:

• =If(ProductCategory = 'Toiletries' or ProductCategory = 'Fruits', 'Consumable', 'Other'), to return the values required.

You can see from the results that the if function evaluates the expression and returns the values required. For example, products in the category Fruits or Toiletries are classified as Consumable.

Overview

Use the if function to categorize a dataset of incident reports.

Load script

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

The load script contains:

• A dataset which is loaded into a data table called Example.

• The following fields in the data table:

  • Date

  • Location

  • Incidents

Example:
LOAD * inline [
Date, Location, Incidents
1/3/2016, Beijing, 0
1/3/2016, Boston, 12
1/3/2016, Stockholm, 3
1/3/2016, Toronto, 0
1/4/2016, Beijing, 0
1/4/2016, Boston, 8];

Results

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

• Date

• Location

• Incidents

Create the following calculated dimensions:

• =if(Incidents>=10, 'Critical', 'Ok' )

• =if(Incidents>=10, 'Critical', If( Incidents>=1 and Incidents<10, 'Warning', 'Ok'))

Overview

Use the if function to categorize dates.

Load script

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

SET FirstWeekDay=0;
Load 
Date(MakeDate(2022)+RecNo()-1) as Date
Autogenerate 14;

Results

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

• Date

Create the following calculated dimensions:

• WeekDay(Date)

• =If(WeekDay(Date)>=5,'WeekEnd','Normal Day')