KNIME Expressions Nodes Article 2: join()
Welcome to article #2 in my series focused on KNIME Expressions nodes. In this video I will be covering the join function in both the and Variable Expressions nodes .
Download the follow-along workflow here: https://hub.knime.com/knimetips/spaces/Public/latest/Column_Expressions_KNIME_TIPS
join() is a simple but powerful function that will help elevate and simplify your work through its ease of use and flexibility.
If you have the follow along workflow just execute the Table Creator node to get started. If you don’t have the follow-along workflow download it from the KNIME Hub here.
I put together a dataset that mirrors what you might find when you export various kinds of usage data. You will see names and job titles in addition to some number columns that include days active, employee id and more.
Double click the Column Expressions node and take a look at the first column. I’ve already named it uniqueID. If you’re wondering how to add a column in the Column Expressions node, please click the + or see the first article in this series KNIME Expressions Nodes Introduction.
Select the uniqueID column.
The first expression is a join allowing you to join data together. In a lot of projects, I often need to create a unique ID or join data together for something else (such as a combination City, State field). The join takes many arguments and when you find it on the + function dropdown you’ll see a short description of the function with a few examples.
In this first example I want to create a uniqueId column that is a join of the Name and empId columns.
Double click join() from the list you will see the function name followed by open and closed parenthesis added to the Expression Editor. Click inside the red parenthesis. Since I want Name first, I select it from the + column dropdown. I want the uniqueId to include a dash between these two columns. To insert additional characters, like this dash, I simply add a comma after my Name column, and add the dash in quotation marks followed by another comma. Then I just do the same step again with the empId column.
Here is what your expression should look like:
join(column("Name"), "-",column("empId"))Unless you add a space with the additional characters you’re joining, your new joined string will be a continuous combination of whatever you placed in the join function. So, if you didn’t add a space, there won’t be one. Additionally, if I wanted to join my columns with a number, I can just add another comma and the number outside of quotation marks.
It would look like this:
join(column("Name"), "-",column("empId"), 42)We’re not quite done yet. While there is not necessarily code completion in the Expression Editor the syntax highlighting comes in really handy and if you take a moment to read the errors generated you can generally troubleshoot syntax problems yourself.
If I hover over the red squiggled line in the expression, I’m told that a closing parenthesis is missing. Once I add it, the expression re-evaluates and the red line goes away. To quickly evaluate for yourself that the expression is working as expected just click the Evaluate button.
After I click Evaluate I see a return of the FIRST ROW from my dataset with the expression. If the first row of your data is missing or doesn’t meet the requirements of your expression this quick peek may not yield expected results.
You’ll notice that in the top portion of the configuration window the first few characters of your expression is visible in the Expression column. This can come in really handy when you’re troubleshooting or have a lot of expressions in one node.
To demonstrate some of the power and flexibility of the Column Expressions node I’ve also included a few examples of JavaScript based approaches to joining string data.
NOTE: Before executing, remember to adjust the options in the Error Handling tab if you want the node to fail on a script error or invalid columns.
Let’s leave this column as uniqueId and leave it as a String datatype. Take a look at the output. It all looks good, the join did what we expected!
Moving on in the workflow I’ve used a Table Row To Variable node to convert the Name, Days Active and empID to variables. This node just grabs the first row of my data and creates new variables based on the column names.
Variables in KNIME allow you to work with data that changes or might be dynamic based on user input, API responses and more. Understanding variables in KNIME will help you unlock some incredible functionality that will elevate your workflows. For the time being, I am just highlighting the shared functionality of these two expressions nodes.
If I double-click the Variable Expressions node, I see a very similar layout to that of the Column Expressions node, except that the + column is missing. Also, the type output options from the Variable Expressions is extremely limited compared to the Column Expressions node.
The join() function works the same way and here you can see that I joined Name a dash in quotation marks, empID, a “+”, and Days Active and am outputting this new column as a String variable called joinExample. I will execute and examine the output of the node and I can see that I now have my new string variable called joinExample formatted exactly how I wanted.
That’s it for the join() function in the Expressions nodes. If you have questions or comments, please leave them below. Don’t forget to watch the KNIME Tutorials video and catch up on any articles you may have missed on knime.tips. If you’ve enjoyed this article please reach out! Happy KNIMING!
Originally published at https://knime.tips.
