Heartstrings How-to Guide
What is Heartstrings?
Heartstrings is a tool used for concatenating lists of data. It can be used to build complex lists and strings that are difficult and time-consuming to create in a text editor. Exactly what Heartstrings can do and how it works can be hard to put into words (and the UI is admittedly a bit obtuse), but once you get the hang of it, you may find it to be very handy.
A Simple Example
Let’s say you’re a SQL champion and you need to query the user table for a list of users. You’ll need a SQL statement that looks like the following:
select * from user where user_id in ('usera', 'userb', 'userc', 'userd');
The list of usernames was provided in a spreadsheet, like this:
userA
userB
userC
userD
Normally, you’d need to crack open your favorite text editor, find a way to surround each item with a single quote, collapse each line into a single line separating each item with a comma, and then surround the whole thing with parentheses. Oh, and the usernames by convention are supposed to be all lowercase.
Heartstrings is here to help!
Heartstrings initially loads with an example very similar to the string you need to produce. Start by pasting the list of usernames into the second data text box:
The output is created by concatenating each data list together from left to right. The outputted list is as long as the longest data list and the shorter lists repeat themselves.
After simply pasting the list of usernames into the initial example, the output looks like:
This is helpful, but now you need to collapse each outputted line, separate each item with a comma, and surround the whole thing with parentheses. Check the Join box. The default prefix, separator, and postfix are exactly what you need:
The output is now the comma-separated list you need:
Remember that all of the usernames need to be in lowercase. Expand the Transform box under the data list containing the usernames and check the Lowercase transform:
Now the output contains all lowercase usernames:
You can even add the rest of the SQL statement directly to the join portion:
A More Complex Example
Now, expanding on the previous example, you also need to query by create_date to try to find a set of duplicates. Start by changing the join portion to create an OR separated list:
To make the example shorter, this time the list will consist of two users: userA, and userB. The create dates are:
12345
67890
Add additional data lists to incorporate the create_date list and field names to create the final output: