String and array conversion: Part 1

Unit introduction

Hey there! Welcome to Unit 3 of our Functions course.

In this unit, you’ll learn about some interesting array and string functions that allow you to modify data to match your needs.

You’ll start by spreading the functions in multiple modules to see how each one works, but at the end, you’ll see how to join these functions together and optimize your scenario.

You’ll explore the merge(), first(), split(), replace(), and length() functions, as well as revisit the map(), sort(), and sum() functions covered in Make Basics.

By the end of this unit, you will understand how to create complex formulas that modify your data significantly, and as a result consume fewer credits!

Sharmila’s project

Let’s dive straight into our use case which will introduce new functions: Sharmila is a data analyst who’s recently heard about Make and wants to use it for her first project.

She’s been tasked with writing a report on sales for a big bookstore chain across multiple regions.

The data that she has to work with is:

the store name in each region
the top salesperson from each store
the top selling book from each store

Her client requires the following from the report:

the total number of bookstores
the total revenue of all bookstores
the top salesperson from all bookstores

This is achievable using functions. So let’s make a start on this!

Sharmila’s project

The API she’ll be using needs to be requested once per region, so she decided to start her project with some Parse JSON modules to simulate the output of the API first.

For this use case, we will say that there are 2 regions, each containing 2 stores. Your aim for this unit is to build the report just as she did. We will introduce new functions along the way.

To get started, create a scenario from the following blueprint before you proceed.

You can download template below (select one of the following):

ZIP file – click to download
JSON code – click to open, then right-click and Save as to download

Name your scenario Make Intermediate

You can run the scenario once to check the structure of the data you’ll be working with.

Here you can see what the mock API response looks like (the data she has to work with), and what is the goal Sharmila is trying to achieve.

API Data

The API data is structured as follows:

Bundle 1: (Collection)
└── stores: (Array)
    ├── 1 (Collection)
    │   ├── storeName: Academake Bookstore
    │   ├── revenue: 10392.17
    │   ├── topSalesperson: (Collection)
    │   │   ├── name: Julia Fazeres
    │   │   └── booksSold: 29
    │   └── topSellingBook: (Collection)
    │       ├── title: Do Automators Dream of Electric Sheep?
    │       ├── id: 3243
    │       └── copies: 67
    └── 2 (Collection)
        ├── storeName: Maker & Booker
        ├── revenue: 13274.75
        ├── topSalesperson: (Collection)
        │   ├── name: Dwayne Maker
        │   └── booksSold: 68
        └── topSellingBook: (Collection)
            ├── title: The No-coder's Guide to the Galaxy
            ├── id: 6124
            └── copies: 59

Each Parse JSON module represents a region, and will contain an array of stores. There are 2 regions and each region contains 2 stores.

Each store has a name, revenue, top salesperson and top selling book.

The topSalesperson is a collection and contains the name of the person and the number of books sold in the period.

The topSellingBook is also a collection, and contains information about the book: title, ID and number of copies sold in the period as well.

The Generated Report

The final output of your scenario should look like this:

Bundle 1: (Collection)
└── Text: Number of Bookstores: 4
└── Total Revenue: $58,702.77
└── Top Salesperson: Dwayne

The first version of this final report, which you’ll expand on in the next unit, must include the total number of bookstores, the total sum of the revenue and the first name only of the top salesperson.

The top salesperson will be determined simply by the number of books sold. Whoever sold the most books wins, regardless of the overall performance of the bookstore in which they work.

Getting the values – the merge() and map() functions

Let’s begin building this use case. Remember to download the template in ZIP or JSON format. Before generating the report itself, you’ll need to get the values you’re looking for. Work through each stage before you continue.

The `merge()` Function

If you have run the initial scenario already, you can see that each of the JSON modules is outputting separate arrays. If you haven’t done this already, click Run once.

The modules labeled Region 1 and Region 2 represent each region. Since we only care about the data as a whole, let’s start by joining these arrays together into a single array, by merging them.

The easiest way to do so, especially useful when dealing with arrays that have the same structure, is to use the merge() function.

To make the output of merge() clearer, use a Set Variable module and create a variable named merged_stores to hold this new array. In Variable value, type merge() and map the arrays of the 2 stores, separated by a ; symbol.

Remember to close the function with a ), otherwise it won’t work!

If you run the scenario again, you will see the output of this new module will contain 4 items within the array. Where you had 2 arrays, each with 2 items before, now you have a single one with 4.

The `map()` Function for Top Salesperson

Now, let’s focus on getting the top salesperson’s name, which is the trickiest part of this unit.

First, you should simplify the data you’re dealing with, so let’s start by generating an array only with the salespeople information. You can do that using the map() function.

By mapping the topSalesperson collection, the new array will contain the values of each collection: the name, and booksSold.

Give it a try on your own.

When using the map() function, remember that when adding a function, you can hover over it to display the tooltip. The map() function will return a primitive array, meaning it uses just one value from the array.

The complex array in this case is merged_stores. A complex array contains several elements inside it, recognizable by a small dropdown arrow in front of it (e.g., merged_stores[]).

The key is topSalesperson. You can get the raw name by hovering over the topSalesperson item. Any values you type for your key within mapping must match the value exactly.

Run the scenario. Let’s look at the resulting array:

The array structure originally contained 4 instances of the storeName, revenue, topSalesperson (Collection), and topSellingbook (Collection).

The new array structure (topSalespersonArray), now contains 4 instances of the topSalesperson (Collection).

Creating a new array makes this data easier to work with going forward. Let’s move on to the next part of this exercise.

Calculating Total Revenue with `map()` and `sum()`

Now, for the total revenue, let’s start with map() again. If you can isolate the revenues into a simple array of numbers, then you can use the sum() function to add it all up!

That’s exactly what you need to do for this one, so you’ll need 2 functions: first map() then sum().

Run the scenario up to this point, you should have the following total revenue displayed in your output bundle.

Sorting values – the first() and sort() function

Let’s continue the next step of this exercise. The next objective is to find the top salesperson, using the array you have just created.

Now that you have a list of all the salespeople in your topSalespersonArray, you can work on finding the top one. The easiest way to do that is to make sure that your array is ordered by the number of books sold. Then, you can use the first() function to get the details of the top salesperson. You can achieve that using and combining two functions: sort() and first(). These are located among the Array functions.

Definition: The sort() function sorts the values of an array by a specified parameter (e.g., booksSold), producing an array sorted from most to least. The first() function retrieves the first value in an array. When nested, it retrieves the first value of the result of the sort() function.

See if you can figure out this function yourself: name your new variable topSalesperson.

The function to achieve this is: first( sort( 4. topSalespersonArray ; desc ; booksSold ) )

Let’s break down what is happening in this function: The sort() function uses the topSalespersonArray as its parameter. The next step after ; tells the function how to sort this array – desc (highest to lowest) or asc (lowest to highest). Finally, the key is booksSold, this is the value that will be used to sort by. To summarize what we are telling this function to do: sort this array, in a descending order, by the booksSold value.

Next, you would use the first() function. This simply returns the first element of the array. The example here shows the first() function containing the sort() function. When you embed (nest) functions within functions, it will always process the nested functions first. In this example, the first() function will return the value of the result of the sort() function.

Run the scenario once more to check the results. The output should be similar to this:

Bundle 1: (Collection)
- topSalesperson: (Collection)
  name: Dwayne Maker
  booksSold: 68

The module you just created should have a collection with information about the top salesperson now (named topSalesperson). But remember, all you want is the first name. In order to get it, you’ll need three functions to extract the first name. Let’s continue with this exercise in the next part.

Modifying a value – using split() and generating the total revenue

Let’s continue the next step of this exercise. The next objective is to retrieve the first name of the topSalesperson, and calculate the total revenue.

Work through each stage before you continue.

Retrieving the First Name

In order to get the first name, you’ll need 3 functions to extract the first name: get(), which you should also be familiar with by now, split() and first().

split(), as the name suggests, will separate an array into multiple parts. What it does in practice is convert a text value to an array, using the specified separator character. For example, split(1,2,3,;) gives you an array with 3 items: 1, 2 and 3.

Name this new value topSalespersonFirstName.

Whenever you’re transforming data, it helps to think about what you want to achieve, and break it down in smaller steps. This becomes easier the more you get familiar with functions!

Give it a go and we’ll review the answer on the next page. The objective of this task is:

Get the full name out of the collection.
Separate (split) the full name into individual parts. Tip: you can split on the space character.
Get the first name.

Note: if you wanted to get the last name, Make has the last() function that can be very handy as well!

Did your function match what is displayed here? If not, copy what you see on this image. Once again, let’s break down what is happening:

get() will retrieve the name value from the topSalesperson array. This would be Dwayne Maker.
split() will split Dwayne and Maker, as we have specified using the [space] keyword. This results in Dwayne / Maker.
first() will retrieve the first of the new values produced in split().

This will provide an output for topSalespersonFirstName as Dwayne.

The variable value for topSalespersonFirstName should be: first( split( get( 13.topSalesperson ) ; space ) ).

Great work! You have used several functions so far to:

merge 2 arrays
create an array for the top salesperson
identify the top salesperson
format their name

The important thing to remember is you have created this new array, and will use it in the latter half of this scenario. You’re halfway through this build already!

Next we’ll look at calculating the total revenue.

Generating the Total Revenue

Now, for the total revenue, let’s start with map() again. If you can isolate the revenues into a simple array of numbers, then you can use the sum() function to add it all up!

That’s exactly what you need to do for this one, so you’ll need 2 functions: first map(), and then sum().

Have a go at creating a variable named totalRevenue, we’ll explore the answer in the next step.

By now you should be getting a feel of combining functions, and your new mapping should look like this image.

Run the scenario up to this point, you should have the following total revenue displayed in your output bundle. The output should total 58702.77.

Now you have all the information you need to generate the report. Let’s make a start on it!

Generating the report

Great work so far! Let’s continue with building the report! Work through each stage before you continue.

Calculating Total Stores

The last value you need is the total number of stores. Since each item in the merged_stores array is one store, you can tell how many stores there are by simply checking how many items are in the array.

The length() function does exactly that! Go ahead and add either a Compose a string or Set variable module. The output of this module will create the value that you use for emailing your final report.

To keep this output for the report nice and tidy, use text to add a label for the total number of book stores. Map the merged_stores item using the length() function. For example, in your text field, you might have: Number of bookstores: length(3.merged_stores).

As expected, once you run the scenario, the output will look like this: Number of Bookstores: 4

Combining Other Values

You already have the other values, so you can combine them in the same module.

In your text field, create labels for the Total Revenue and Top Salesperson, and then map both the totalRevenue and topSalespersonFirstName items that you have created. For example:

Number of Bookstores: length(21.merged_stores)
Total Revenue: $ formatNumber(8.totalRevenue; ; 2; ; ,)
Top Salesperson: topSalespersonFirstName

You can optionally add a $/£ sign before totalRevenue to display the currency, as shown in the image. You can also use formatNumber() if you’d like to tidy up the total revenue value.

Formatting the Email Report

Alright, your report is ready now! One last thing to add to the scenario though: Sharmila wanted to automatically send the report to her manager once it was done. To test it first, she set up a Gmail → Create a draft email module.

However, the email body was coming on a single line. That doesn’t look good!

The problem here is an exception of Gmail and a few other modules. Since the content expects HTML, newlines are ignored (as they often are in HTML). To get around this, you can replace the newlines with a special HTML tag so they are displayed correctly:  .

Let’s have a quick review of how this function works. The replace() function looks at the output value of the entire scenario. When it detects a new line (for example, between Number of Bookstores: 4 and Total Revenue: $58,702.77), it will replace this with a different value that you specify.

In this example, the expression replace(28.Text; newline; ) is used. This inserts   for every newline, which will create a brand new line for any content being viewed in HTML.

This means that after the replace() function, the output will effectively be structured like this: Number of Bookstores: 4 Total Revenue: $58,702.77 Top Salesperson: Dwayne

Great work! Run your scenario and see the result in your Gmail drafts. You should have a pretty big scenario by now, as well as a nice tidy report that looks like the image displayed here.

Let’s do a quick recap before we wrap this unit up.

Use case review

Let’s run through Sharmilla’s use case that we have just built together for a quick reminder of what each stage of this does.

Stores 1 & 2

The first 2 Parse JSON modules each contain data that represent 2 stores.

1 region (Array) = 2 stores (Collection)

Merge Stores

This module will use the merge() function to merge both stores together.

1 single array (both regions), and 4 collections:

region 1 store 1
region 1 store 2
region 2 store 1
region 2 store 2

Map All Top Salespeople

This module uses the map() function to search the merged_stores array, and extract a new array containing the data of the topSalesperson: their name, and the booksSold. This array is called topSalespersonArray.

1 array, containing 4 collections composed of the name, and booksSold.

Find the Top Salesperson

This module combines the first() and sort() functions. It will first sort the new topSalespersonArray in a descending order, by the amount of books sold.

Then it will retrieve the first value of this.

This will save a variable named topSalesperson, a collection containing a name and the amount of booksSold.

Get First Name of Top Salesperson

This module utilizes get(), split() and first() to use the topSalesperson variable:

get() retrieves the value.
split() finds the name field, and splits it in 2 when it detects a space.
first() retrieves the first value of the split() stage.

In this instance, it will produce ‘Dwayne’.

Sum Total Revenue

This module uses map() to search the merged_stores array, and retrieve the revenue fields from each of the 4 collections. The output of this without the sum() function, would be an array with 4 separate values:

16784.32
18251.53
10392.17
13274.75

However, when combined with the sum() function, this adds all 4 values together, creating the totalRevenue variable of 58702.77.

Generate Report

This module will produce the number of bookstores by using length() to give a result of 4.

[Optional] The formatNumber() function will tidy up the totalRevenue variable, so it goes from 58702.77 to 58,702.77.

And the topSalesperson variable will be mapped.

All contained within a single value.

Gmail

Finally, the Gmail module will create a draft of this report - this could also be sent instantly via email, but it’s safe to confirm the scenario works before committing to an email.

The presentation of this will be tidied up by utilizing the replace() function, to replace any new lines with HTML, to add better formatting.

This concludes Sharmilla’s use case, as well as the functions you’ve learned in this unit - great work!

Unit introduction

Sharmila’s project

Sharmila’s project

Element - Selectable image 3 – modal

API Data

The Generated Report

Getting the values – the merge() and map() functions

The merge() Function

The map() Function for Top Salesperson

Calculating Total Revenue with map() and sum()

Sorting values – the first() and sort() function

Modifying a value – using split() and generating the total revenue

Retrieving the First Name

Generating the Total Revenue

Generating the report

Calculating Total Stores

Combining Other Values

Formatting the Email Report

Use case review

Stores 1 & 2

Merge Stores

Map All Top Salespeople

Find the Top Salesperson

Get First Name of Top Salesperson

Sum Total Revenue

Generate Report

Gmail

The `merge()` Function

The `map()` Function for Top Salesperson

Calculating Total Revenue with `map()` and `sum()`