Low-Code Data Science and Analytics with KNIME

Hi there, Data scamps and Data Champs, this is Richie. Now, I love writing code, but having a point and click interface is often easier for people to get started and it also makes it easier for you to collaborate with non technical colleagues. So today we're looking at the low code data platform knime. And I must confess, I discovered this software maybe eight or nine years ago. And it was only when I started putting this webinar together that I realized that, that actually it's not pronounced Knime. And so obviously I've just been making a fool of myself for the last decade or so. So anyway, today we're going to learn how to get started with Knime and we're going to try some exploratory data analysis. So our guest is Emilio Silvestri. He is a data scientist at KNIME itself and he's got a lot of experience in creating courses and around data and visual programming. And in particular he created the Introduction to KNIME course on datacamp. So without further ado, please take it away, Emilio.

Exactly. Thank you, Richie. Welcome everybody. Thank you for the nice introduction. Thank you for pronouncing Knime the correct way. It's not, it's probably the hardest thing for beginners and yet very happy to, to be host today for this webinar and for this actually Code along or low code along, because we are going to see that with knime. The main point of KNIME is that you're not really coding and we're seeing it together. If you can have my slides put up, I can start right away with showing you today's agenda. So we're going to start talking a little bit about Knime Analytics Platform, what it is, what are the main advantages of using it, especially for the ones of you that really don't know or never heard about it. I see from the chat that some of them are already familiar with that. So good for you. Then we are having a session of Code along where I'm building a workflow with you and you can follow along with Nematics Platform locally. And then in the end, as Richie said, we are going to have 10 minutes of Q and A where I can answer all your questions. All right, so let's jump into that. Let's start with Knime Analytics Platform, a broad introduction of that. First of all, what is KNIME Analytics Platform? The definition that we have is that it is a tool that you use to make sense of your data. It's a very broad introduction. It's a very broad definition. I know, but really it covers Everything that you can do. Because with NEMA analytics platform you can perform operation for data analysis, for data science and for data engineering. And it really covers you from accessing the data to transforming it, manipulating, creating visualization, creating, reporting, and we will see also to do some machine learning, deep learning, generative AI. It is really an umbrella platform for all data related. The other important part is that it is open source. So for the ones of you that have already downloaded, you can see that it's free, the source code is actually out there, so you can actually have a look at it. And so you don't have to pay to pay anything. And it's completely open. And then the other important point is that it's based on the visual programming paradigm. It means that there is no coding required. If you don't want to. Let me dig a little bit more inside this concept of visual programming. So if you're dealing with data, you probably will have to deal with different technologies. You will have to write some python code to access the data, or maybe write some SQL queries to access a database, or perform some operation r some Java there, write some API requests if you are outsourcing your operations to an external provider. So all these kind of technologies, you need to more or less be able to interact with them. And also you must be able to make them talk to each other, right? But what we do with visual programming, with visual workflows actually is that you let the workflow do the interaction for you. So what you are going to do is using a single entry point, so one single technology, in this case the visual workflow of knime. And then under the hood is that workflow that takes care of connecting to SQL, of writing to an API or generating some, or executing some code that will let you operate on your data. Now of course you can see the benefits here. The first one is that the workflow becomes your programming language. So it's not that there is no programming involved, you still program. It's not just the usual thing that you think. The first thing that you think about programming because we will see that you program with some building blocks, but it's still a programming language. Then the other thing is that you abstract the implementation. So as I said, you don't interact with those technologies one by one, but, but you have one level of abstraction to interact with them. And then since it's low code, no code approach, you can still embed some code snippets if it's needed. So if you want to really include some python code, for example, you can still do that within the workflow. Now I've mentioned the word workflows many times already. I will mention it again a lot, but let me explain it more. The two basic concepts of Knime analytics platform, which are nodes and workflows. Nodes are the, let's say the main unit of Knime analytics platform because they let you perform tasks on data. We will see that we have a bunch of nodes like more than 4,000 nodes, something like that, but they all follow the same structure. They all have some input and some output ports. When you kind of pass the data inside and then in the output you get the data that have been elaborated, let's say. And then you have a status here down the node. I hope you can see my pointer down the node you can see that there is a status, a traffic light that lets you know what is the status of that specific node. And the status can be not configured, configured, executed or if there is an error with not configured, it means that something is wrong, so you need to. Something is missing. So you need to either provide the data or provide some configuration. Configured means that the node is ready but hasn't been executed yet. And with the executed state, it means that the node has been executed and has produced something in output. Has produced the data in output. And of course if there is an error, there is something that went wrong and you need to investigate. Now building your workflow means putting together different nodes to create a full pipeline. So in this case you combine different nodes, for example the CSV reader node, the column filter node, the row filter node and all of them take the name of workflow. And the workflow is actually what you are going to build today in today's session. And then in the workflow, basically you have to always read some data. So put some data inside the workflow with specific nodes and then the data get passed from one node to another to be able to be processed by the nodes. Now to anticipate some of the questions that you might have. I created this slide because I think it might be relevant at this point, right in the beginning, which is where is KNIME used? Like why would I bother using Knime and how can it benefit to my career? What I can tell you is that Knime is used across industry. So we have users in retail, manufacturing, in finance, in marketing, in research, a lot of them. And within those industry is also used cross department. So from the data specialists, so from the data analysts to data scientists within those industries, but also some business and domain experts together with some just end users that decided to move on from, for example, spreadsheets and decided to embrace visual programming because it's easier, it's more accessible and it's more transparent. I'll leave you there a link if you want to explore more solutions by industry and department and then you can see how actually Knime is used in the different industries. And now, without further ado, let me do a couple of slides, more to introduce the code along and then we will be able to start coding together. So what we are going to realize today, what we're going to produce today is we're going to analyze some bike sales data. We have two data Table one about the sales. So we have a date, we have the customers, and then we have the product that each customer bought and how many times, for example, this person from Canada bought eight times this product here, AC100. And then for every product we have a second table that shows the different, that shows more information about that product. So the AC100 is actually a bike accessory and it's a bike rack and it costs €120 or dollars. Whatever you prefer, or whatever you prefer. What you're going to do with this data, we're going to do the full data, or the classic data pipeline, let's say. So we're going to access the data, merge the two different tables, aggregate the values, we're going to create new features and we're going to visualize the data to be able to answer some of the questions, some of the questions that we might have on those data before we move. You need to install Knime Analytics Platform. If you haven't got it from all the messages that you got from the chat, go to knime.com, download and download the package for your machine, Windows, Linux or Mac, whatever you have, and proceed with the installation if you haven't done it. And then once you have it, you also have to download the KNAR file again from the link in the chat and then we will import it to your local space and with that you will get both data and workflow in one go. So you don't need to take the data somewhere else. Everything is in there in this KNAR file. It stands for KNIME Archive file. So if you're ready, let's move to Knime Analytics Platform and let's start the code along. Let me do that like here. So when you open Analytics Platform, this is what you're going to see for the first time. You are in the recent tab, you can see that here there are some welcome workflows. So Some blueprints workflow. What I want you to do is to move to the local space here and then click the second button, this one, which is import workflow. Now we can browse and look for Knime exercise and data KNR file, which is the one that you have just downloaded from the link. Click open and now you should see the knime codalong folder. If you open this folder, you can see that inside we have different things. We have two workflows. One is the exercise workflow and one is the solution workflow. And then we have a data folder where if you double click you can see that there are two CSV file and one Excel file saved. Now let me go back and open the exercise workflow with double click. What you will see now is that the workflow opened in this canvas we call it, and then you can start to interact with it. This workflow is actually empty. There are no nodes, as you can see. All you can see is that there is some text, but those are just annotations that I left there to let you know what we are doing and also to remember myself what I have to show you, of course, but there is nothing. So we're going to build this workflow together. Actually, you can see that it's already divided into different levels. We have a warm up and then level one, two and three and some bonus. So we're going to proceed step by step and let's see see to which level you're able to, you're able to reach in today's session. So let me zoom in and let's start with the warm up section. You can see that every level has some questions that we're gonna try to answer together today. And then here is the operation that we're gonna produce by putting some notes together, basically. So the first thing that we have to do in this workflow, but probably in any other workflow you're going to build in Knime, is to read the data because otherwise we can do anything else to read the data. I can show you that it's super simple by going to the left side third menu here. And this is called the Space Explorer when you basically can explore all the workflows that you have in your workspace. Here is the open one, the exercise workflow. I want you to go to the data folder, double click and then select the BikeSales CSV file and drag and drop it to the workflow. Now what happened is that this file, this drag and drop operation that we've done, created a new node inside our Workflow, specifically a CSV reader node. This node is already configured to read that specific file that we have dropped into the workflow. So what we can do, and how do I know that it's already configured, is because if I zoom in, the traffic light downstream down the node is yellow. It means that it's configured and it's ready to be executed. So to execute the node, to put it to the executed state, to the green state, let's say I only need to click display button. And now the node is green. And. And we can see down in this table output part that the node has produced something. The node has produced a data table, which is exactly the data table that we have that we expect from the CSV file. So we have the date column, we have the different information about the customer, especially the customer, age, the gender, the country, the product that they have bought, and the quantity, how many of them they have. So all this, I have accessed some data by just dragging and dropping the data inside the workflow. And you can do that again if you now scroll down for the product info. So I just need to take the productinfo Excel file and then drop it. It will create an Excel reader node. So a new node that is able to process that kind of data, execute it. And now I have the information about the bikes down here. I have all the information here. So every time that I select. Let me zoom out. Every time that I select a different node, you see that this table down here changes, gets updated with the output of the specific node that I have selected. So simple so far, right? I hope you were able to follow. I just took the data and drop it into the workflow. Now that we have the data, let's try to answer some of the basic questions that I have put here. For example, what is the age distribution of the customers and which country bought the most products? So for the first questions, we have many ways to answer this kind of questions each. If you are doing a very, very shallow analysis, I highly recommend you to click on the statistics tab here. When you select the note, you have the table, but you can also click statistics. And in here you can see that for every column you have some basic statistics already calculated. For example, for the customer age column, you already see that there are 66 unique values. The minimum is 17, the maximum is 87, and so on. We have, you have the mean, the most common values, et cetera. So without putting together many other nodes, putting together other operations, you can just have a look at the statistic table for any data that you have, and it will show up, it will show you basic statistics. If you are interested in the age distribution of the customers though, then we can create actually bar chart, for example, or an histogram, for example, which is able to show us what is the distribution visually. To add a new node to our workflow, what we have to do is to go to the node repository here, which is the second menu here, and then if we scroll down here, we can see all the nodes that we might add to our workflow. If we scroll down, we have the histogram node that we can again drag and drop to our workflow. Now this node has been added to the workflow, but you can see that it is red. It is not configured, so it's not executable. I can't click this here. And the reason is that it missed some data. So what are we going to create the histogram on? So we are missing something for that. And you can imagine that to provide the data to the histogram, we have to take take the output of the CSV and connect it to the histogram. So what happens now is that this data table created by the CSV reader node will flow inside the histogram node and we will be able to use it to create a new visualization. So if I now click Play, this node is executed. And if I click, I can see that it has created a view inside here. So this view is not just. So in the bottom you can not just see tables, but you can see anything that is created by any node in your workflow. In this case, a nice histogram like this, we can also show which country bought the most products. So for example, for that we might want to create a bar chart. And again, we can do the same thing. Let's drag and drop the bar chart and connect it to the CSP reader. Now, if I click Play, if I click Execute, the node executes something. But that's not what we expect, right? That's not the country that bought the most products. This is just something that the node has created with the default configuration. So if we want to change the behavior of this node, if we want to actually configure this node with what we wish it creates, we need to click on this cog icon with this configure icon here, and it will open a new window where we can actually select all the options that we have to create this visualization as we wish. It looks a bit messy now, just because it's been executed with the default configuration, but it's gonna make sense in a minute, so just follow me. You can close this one and you can see that here on the left side you basically just have the preview of what is gonna be your visualization. And on the right side you can select the different options about this visualization. So what you have to do to calculate the country that bought. The country that bought. What do we have to calculate the most Products you need to select as category dimension. You need to select country and you will see that this will reset somehow. So you select country category dimension as aggregation. You select sum and then here as include, exclude. You only want to do the sum of the order quantity. So you want to exclude this customer thingy and you just move it, you select it and you move it to the left side of this menu. So you have category, dimension, country, aggregation, sum and the sum of what? The sum of the order quantity column. So if you now click save and execute you will see that this node has produced a nice bar chart where you can see that the United States bought more than 48,000 of items from us. And then following is Australia with 27 and so on. You just have to. I hope you were able to follow so far. If not, please have a look at the configuration. But it's super simple. The category dimension is the country. So you just select what you want to see here in the bottom, the aggregation is the sum and basically what it's going to do is going to look at the order quantity and it's going to sum them up for all the different categories for all the different countries. In this case if you click OK it will close but you can still see the output of this node in the bottom. So that was the warm up. I hope now you know how to drag and drop the data, how to drag and drop the nodes and how to connect them and configure them. And if you're ready we can move to the level one of this, of this code along. So in the level one, let me move a little bit to the right, we want to answer another question which is what is the best selling product? So we have all those information about the product, all those, all the sales that we have that we have for different days from different countries for different products as well. So if you scroll down you can see that the products are different and then what we want to know is basically the product, we want to sum up the quantity that we sold for the different products and see which products sold the most basically. But what we are missing is that what is this product? What is this AC105? What is this AC100? That information is in this second table where we see that AC100 is the bike rack and BK135 is a bike, and so on. So what we want to do is to bring together those information into the same table. And if you're familiar already with spreadsheet, probably you know the Vlookup node or the value lookup, sorry, the value lookup operation where basically what we want to do is to match the information about one column with another column of another table and bring them together into the same table. And to do that we will use the value lookup node in Knime. So let's go here on the left and look for value lookup, lookup and again drag and drop the node inside the workflow. Now this node looks a bit different because it has one and two input table and only one output table. So in the input table you can imagine that we will connect the output of the CSV reader and the output of the Excel reader. So now we have two tables put inside this node, but we still cannot execute it because it needs some configuration again. So let's open the configuration window and let's see what we need to select. We need to select a lookup column and a key column from those two tables. So from the first table, the lookup column is the one containing the product. Actually, let me select the product the table here, so you can see it. So we want to select this column here. So as lookup column, let's select product and as key column from the dictionary table, we need to select the product code. So what we are going to do, we are telling the value lookup node that when you see the same product and product code from those two different tables, just bring them together into the same row. So let's just make this the selection for now product and product code, click ok. It's yellow, so we can execute it. Now you can see that the data table that we passed to this node evolved into a different in a different state. Let's say. So if before we only had the date, the customer, the country, and the product number. For example, if we now scroll to the right, we can see that for every product we also have the information about the price and about the category. So before our table stopped here, and now, thanks to the value lookup, we added those five new columns for all the rows of our table. So very simply, we have taken one information from one table and put it together into the other one. And now we can actually we are able to answer the question what is the best selling product? Because all we need to do is basically calculating the again the sum of the order quantity for every product and see the one that sold the most. For that we will use the row aggregator node. So I go again to the nodes, look for row aggregator and drag and drop it to the workflow again. Now the data table is, we will take the last one of course, the one produced by the value lookup and connect it to the row aggregator here. The node says that it's already configured, but let's actually open the configuration window and tweak the configuration a little bit to let it do what we want. Let me open this. So we have to select again a category column, an aggregation method and one of more columns that we want to aggregate. What we want to do is to kind of squeeze our table basically to select for every category. So for every product in this case let's select actually product name. Here we want to sum the order quantity. So for every product, for every bike, for every tube, for everything that we sell, we want to sum all the quantity that that are in this column here. So let's select product name as category column. Let's select sum as aggregation method and then only keep order quantity here on the right. So let's remove customer age and product price to move things around. You can select them and move them or just double click. So make sure to only have order quantity here in this includes list. And now I'm able to execute it. And I can see that my big table actually for 11,000 rows got reduced to only 130 because I have for every row one different product and I also have the sum of the quantity of the sales for that specific product. So we see that this logo cap goes got sold 7,000 times and this bike stand got sold 265 times because the raw aggregator summed up all those numbers. To answer the question what is the best selling product? We just need to click order quantity and this will sort our table in descending order and we will see that the most sold product is actually a water bottle. So it's not any fancy bike, it's just a water bottle with 17,000 units sold and then a patch kit, a Montenegr tube, etc. And that was it for level one. Actually, I think now it's a good time to ask Richie if maybe there are some questions so far or maybe somebody had some problem to follow. Because if not, I will go on with level two and three, which are a bit more Exciting.

All right, yeah, we got a couple of questions and actually before we get to those, I just want to show a comment from the audience. You've got a fan here. So Mia's saying, I finished Emilio's course in Data cup already. Everyone else in the audience, you're playing Catch it with Mir. So yeah, that's good news.

Okay.

All right, so questions then. Oh, actually this question's from Mir saying, I found Knime so powerful and a great problem solving tool. Why isn't Knime as popular as other tools? I'm not sure if you can answer that. And how are you making this available for all people?

That's a great question, actually. And I wish it was more, it was more popular. And I think with this kind of webinar, this kind of what we are, what we are playing on is this kind of events to participate and to let people know. And the fact that it's open source, I think that sometimes let people kind of saying, okay, but so is it actually worth or not? So you never. There's still a little bit of misconception with this open source word. So it might be that can be that it's, it's a pro and a. And it's an advantage and a disadvantage. So I hope that in the future we will see more and more, more and more usage of Knime around different department, different industry. Even more than what we see, what we see now.

Okay, wonderful. So there's another one from Amit. I was just asking, like, how do you go about dealing with crashes and like, what are the system requirements to run Knime? Like, do you need a particularly powerful laptop to make use of this? Or does it depend on how big your data is?

Usually no, it depends what you are. Maybe what you're trying to do. Maybe the database is very big. I don't know. We would need to investigate the specific case, but I run it in different machines and usually it kind of works. If it's a problem with that specific version, then try, try to use, of course, the most recent version. I'm in 5.3.2, I think now or send. Also we have a forum, you can ask for support and usually there are people from the community or actually nightmare that can answer specific problems. Maybe there is something to be set to your specific instance or yeah, I suggest to go to the forum and ask this kind of more technical questions.

Okay, so if you've got problems with crashes or technical support required, then go to the forum. That's where you get help.

And we have support as well. Yes.

All right, wonderful with that. Let's continue on with the rest of the session.

Cool. Thank you for the question. So keep. I hope they will skip. They will keep coming and let's move on to level two. So let's try to do something a bit more. A bit more complex, slightly bit more complex. Not super, super complex, of course. What we want to answer now is which category. Let me swim. Which category generated the most revenues? So we want to see from different categories from. I think we have three categories, accessory, bikes and clothing. Which one is the one that basically generated the most revenues? The problem is that we don't have any information about revenues yet, right? So if you see this data table, we know how many product we sold and then we know how much one product cost, but we don't have the actual revenues of this, of each transaction. Let's say for that we need another node, which is the expression node, that will let us calculate a new feature out of our table to add the expression node. Again, I can look for it. It's here, drag and drop. But I want to show you one more trick, which is the quick node adding panel. So if you just drag and drop your connection and look for a node there, it will be added automatically. So you don't need to look, drag and drop and connect. You can do it with just dragging and dropping and then adding any node that you want. So that's another way of doing it. Of course, you can do the actual common way that you're already familiar with just whatever you prefer. But with the expression node, which is a very, very powerful node, we will be able to calculate a new column, which is the revenue column, by configuring it like this. So let's open the configuration window and let's not be scared about everything that you see. That looks a bit confusing. So with the expression node, we can create custom expression. This is actually a little bit the low code part that we are going to introduce. Although it's not a real programming language, it's not a specific programming language, it's not Python. Python. Everything happens in the expression editor, where we are buying, where we are building our expression. You can use some formulas, of course, we won't use them today. Make sure to have a look at them. Maybe there is something that you might need at some point. But for now we are doing a very, very basic formula in here and then on the left you can see the different columns from the input table that you have in the expression node. So first thing, let's select everything green and Delete it. Because this is just a sample code. What we want to do is to calculate the revenues. So for that we need the order quantity. Let's drag and drop this column here. Let's put a star because we want to multiply it with the product price. Again, drag and drop. So what we are telling this node is that for every row, take the order quantity, take the product price and multiply them together. Very, very simply. We can evaluate the first 10 rows so we can have a look at how it will look. It's kind of a preview. And we can see that in the right, if we scroll, it added a new column which has the result of this operation. So it took the order quantity, it took the product price, multiplied them together and put them here. €960 dollars, whatever for this transaction, more than 2,700 for this transaction, and so on. So it went through every row of our table and calculated this multiplication. So we have the revenue. Actually what we can do, we can change the name and this column. Instead of calling a new column, let's call it Revenue, like this and let's click ok. Now the node is configured and we can execute it. And if we scroll right, we can see that now we have the revenues column with all the. With all the. All the values, all the values filled. Now the expression node is way more powerful than this, of course, but for this, for this operation is more than enough. Let's try to answer the question. So which category generated the most revenues? We have the revenues for every transaction, but again we want to aggregate, basically sum up all the revenues for the different categories and see which one is the one that generated more revenues. For that, let's use. Let me do it like this. Let's use a pie chart, for example, let's look for pie. And this is the pie chart, another visualization node. So we'll be able to answer this question visually. Again, let's open the configuration window. If we try to execute it like this, this probably doesn't generate anything meaningful. We need to configure it. So we need to calculate, we need to indicate the category dimension, which in this case is going to be the product category and then the frequency dimension. So what we want to put inside the slides of our pie. In this case, we want to put the revenues, so product category and revenues. And we want to sum them up. It's going to take for every. Again, it's going to do more or less the same operation that we have seen so far. It's going to take the different Categories. It's going to sum up all the revenues that we have for every transaction. If we click save and execute, we can see a preview and we can see that we have the bikes that generated 72% of our revenues, followed by accessories and clothing. If we scroll down, you can put the name to your pie chart, for example, and call it Revenues by category. You can see that this gets updated right away. You can transform it to a donut chart, which I like better. And again, you have a bunch of other options that you can select for every visualization that you can have a look later. So let's click ok, we have answered one more question. So the level two question, and we know that the category generated the category that generated the most revenues is the bikes category, as we can imagine being a bike shop. And let's now move to level three. So let's see the most complex part of today's session. Now our question becomes a bit more complex because what we want to answer, what we want to answer is how did the revenues from the bikes so only from the bikes evolved over the years for different countries. So we want to filter only the bikes. We want to see the revenues for every year, and we want to see the revenues for every countries. And then we kind of want to plot them probably again to see how they evolved over the over the different years. So let's do that. Let's start from the simple stuff which is the filtering part. Since we only want to keep the bikes, we need a node that takes this full table, takes this full table here and says, okay, there are 11,000 different transactions, but we only are interested in the transaction involving. So all those ones need to go, all those accessories need to go, all those bike clothes need to go. We only interested in the bikes ones. So let me drag and drop the connection and look for a row filter node. The row filter will do the operation for us. So we need to open the configuration window and add a criterion for the filtering. If you click add criterion we can select multiple of them, but we only need one because we need that the column containing the category, in this case the product category column equals bikes. So we can actually type bikes. Let's click ok. So product category, if you didn't follow filter column is product category, operation is equals and the value is bikes. You need to actually write it down. Click OK, execute. And now we can see that from 11,000 rows we ended up with only 26, 21 rows. So I'm also repeating these numbers in case you are trying to just listen to Me so you can check that you're doing things right. You should have a table. We only two 26, 21 rows. And if you scroll to the right, you can see that the product category only involves bikes now. So you have filtered your table. And the cool thing now is that if we go back, we didn't change our our data table. So we can always click on a different node here and see that and see basically how the data table evolved over the different operations that we have performed. And this is actually one of the biggest difference with spreadsheet, for example, because in spreadsheets the operation and the data are kind of overlapping. So we have one data table with the data and with the operation inside it. And in Knime it is different because the operations are a bit more explicit. They're kind of in those nodes. So you can always go back and see. Hey, what happened here? Let me see. The table looked like this and now it looked like this one. Something happened. Let me see what. How can, how can I investigate? All right, cool. So we have filtered the data. We only have the bikes now. And now we want to extract the year. We want to take from this string here that I have 2020 0128. We want to be able to only extract this first part. So only the year of the date. We can do that in many ways. We can use also regex if you want, but I want to show you another way, which is the date and time support. So if you transform this string to a date and type time, because the date now is just a string, if we transform, if we convert it to a date and type time, we are able to extract the year automatically without writing any regex, for example. So let me look for string to date and time. This one, this node, the string to date and time node connected to the row filter and let me configure it. This node just lets you select one column which is the date column. Let me just put the date here on the right and then click guess. Data type and format. This is what it's going to do. This node is just going to parse your string and transform it into a new data type, which is the date format, basically. So date guessed it and type format. Okay, and that's it. Now you can click run and you can see that the data table didn't really change change from before. The only thing that changes that now date is in type local date. While before was string. So now you have the local date here and you can use another powerful node which is the date and Time part extractor. If you drag and drop from the string to date and type, you can see that the third node is the date and time part extractor. Let's add it, open the configuration window. And now we can select a data column which is actually the date, and we can select a specific field that we want to extract from that date. For example, we can extract the year, but we could also extract the quarter, the month, the week, the day of the year, the day of the month, whatever we want. So we can also say let me give me the year and give me the name of the month, for example, or the month number or the week or whatever. Now if I click ok, execute, you know the game. Now if I scroll to the right, we have two new columns, one containing the year and one containing the month. And again we needed to transform the string to this date and type format. So we needed to parse this string. And then we are able to extract all those fields easily. And now finally we, we can create a pivoting table where we calculate for every year. So we want different rows with different years for every year. We want to calculate for different countries the amount, the sum of the revenues that we have. So let me drag and drop again this node and let me look for a pivoting node again. If you're familiar with spreadsheets, you know what pivoting is. So let's try to configure this pivoting. Let's do that together. We have pivots. We have to select pivots, groups, and the aggregation that we want to happen inside the table. So as groups, we want to select years. So every year, every unique year, 20, 20, 20, 21, 2022, etc. Will become a different row in our resulting table. Let's go to pivots, and in here we need to select the countries. So double click and let's bring it to the right. So now every country will become a different column. So we have the years in the rows and the countries in the columns. And then let's move to manual aggregation. Let's select revenues, double click and let's select an aggregation method, which in case is going to be sum. So what we are telling the pivoting node to do, we have configuring it to calculate the sum of the revenues for every country for every year. Now if we click ok, if you've done it with me, click execute. You can do it in many, you can execute the node in many buttons to do that. And then you can see that now the table Changed completely. You only have six rows and seven columns. And it looks like this. You have year from 2018 to 2023. And then you have the different countries, Australia, Canada, France, Germany, etc. In the different columns. And inside you have the revenues for every country. Now, to answer the question quickly, what can we do to calculate this, the different revenues for different countries? We can show that with a stacked area chart. Before that we need to do one more operation, which is the number to the string. To number. No, the number to string, sorry, the number to string operation. So we need to convert basically the year, which is an integer, into a string. And let's do that quickly by just removing everything and putting year on the right like that. So I just changed the type of this column basically from number to string with a number to string node. And then I can create a new visualization which is the stacked area chart. This one with the stackdata chart, I can configure it to show as horizontal dimension the year. And as the different lines I can show the sum of the revenue. So let's select all of them, let's keep them, let's keep the Australia, Canada, France, Germany, blah blah, blah here on the right and select sum here, save and execute. And you can see a nice secondary chart that basically, that basically show you how the revenues went. So in the beginning was slightly going up with the. Who was leading, I think United States was leading the revenues and Australia. Then 2021, nobody wanted the bike. And then in 2022 we went up again. Now I have two more things that I want to, that I want to show you. Maybe I can use one more minute because you might have the question of okay, and what now? So what can I do now with this thing? So am I stuck in Knime? Do I have to continue in Knime? And the answer is of course no. You can actually drag and drop this and look for example for a Writer node and you can select an Excel Writer node. For example, with the Excel Writer node you can take any table. So I've done it with this one. But I could take any other table of my workflow and write it to an Excel file. So I open the configuration window, I say save it here in my documents. I need to create an Excel file that then I can, I don't know, store somewhere, send to my colleague, or I don't know, pass it to another, to another technology, to another product. And then the other question is, can I repeat this operation on new data like now we have done, if we look back we have done many things on our data. We have read it, we have put data together, calculate some new features, filtered, created a pivoting table. But this is just for 2023. What if I want to do it again for next year, for example? Well, the answer is that of course you don't need to recreate this thing from scratch. You just go and look for new data, for example, in this case the bike sales, you can drag and drop it, you can read this new data and then you can just decide, okay, you know what? I don't care about this 2020 stuff. Let's start new and let me give you this new data here. And then you can run the whole workflow again from the beginning to the end. It will run the same operations just on new data, of course. And then you can see that from 2023 to 2024, how the operations went, how the revenues went, for example. And again the Excel writer will write again a new file when you configure it to write somewhere else. And of course, if you're interested in both of them and you kind of want, you're asking, okay, what if I want to put them together into the same table? Well, you can do that with a concatenate node. You just put it here, give all the tables, give both tables. Now you have a complete table with data from 2022, 2024 in this case. And again, let's put the table here, let's execute it again and see how the Stackdira chart now looks up to 2024. So we have put together two different tables and we have run the operation again and again as new data come. I only have one other. I also have one other bonus, but I think we're running out of time probably, which is about the kei. We also have a AI assistant, of course, that is used to kind of helping you generate workflow, answering new questions. For example, what is the node that I need to use? Or what is the expression that I need to build here? Well, you can do that with the assistant. Let me just demonstrate this and then we are done for today. We can answer some questions. Actually, if I look for expression node again and let's say that I want to answer some information about the seasons, but I don't have the season and I don't know how to extract them, then I can just go to the expression editor and click Ask Kai. I will have to log in to my hub, accept terms and conditions, and then say, I don't know how to extract the season. So generate a code an expression to extract season from the month name column. I click run. And then what it does, it will generate. Now, I can see that completed, but it will generate a formula that I can insert in the editor. And this formula in this case takes the month name like this and produce a false producer, produce a season for every different month that we might have. So if I try to evaluate. Actually, let me try to execute. I didn't write this expression myself. It's just that Kai said that January is winter and July is summer. And of course, then if this is completely wrong, I can change it, I can tweak it, but at least it gives me starting point if I don't have any clue. But of course I know that with anything that is AI generated, you need to have second pair of eyes to see if what's generated is correct. All right, so that was it actually, from. From my side, I can't see. Yeah, yeah, I see that. Wonderful.

Thank you, Emilio. That was very cool stuff. I do like that you can do all the kind of common analytics tasks. No code needed, very simple. We've got a load of audience questions. We're probably going to run over by a couple of minutes. Before we go to audience questions, I just want to say, on November 13, we have a virtual conference. So the latest edition of the Data Camp Radio Radar virtual conference is happening. It's called Forward Edition. It's going to be all about trends in data, trends in AI, trends in cloud computing, trends in, like, what businesses are doing with data, how learning and development works. So you've got some sessions of managers, some sessions of practitioners. It's going to be a great session. We've got tons of great guests. So, yeah, please do use the QR code to sign up for that. All right, with that, let's quickly dive into some audience questions. So, so Den asks, can we use KNIME to create a dashboard or would you want to hook it up to a separate BI tool?

All right, nice, nice question. I already have a slide for that because I knew that it would come up. And yes, the answer is yes. So everything that we have created today are single visualizations, but you could anytime put them together into a component, which is another terminology that we use, and all the visualization inside that component will become part of your dashboard. And I could quickly demonstrate it later if you want, but you can do pretty cool stuff and pretty elaborate stuff. So you can put titles, you can put different numbers, and this is, for example, a bar chart. And then you have a pie chart. You can write something and this will create whole dashboard standalone that is also interactive, of course. And yeah, so the answer is yes.

All right, super. So dashboards with Knime, that sounds like a webinar for next year.

Yeah, probably we need a new webinar.

All right, nice. So next question is from Pascal saying, how do you know which node to use for your problem? I see lots of different nodes available. How do you know which one?

Yes, so this was actually a question for a long time and we, we were trying a lot to let people know all the nodes that are out there and then we realized there are just so many that I don't even know all the nodes there. For the most common ones, of course, at some point you will become familiar with them and you will know that you need a CSV reader and a row filter, for example. For all the other ones, I highly suggest you to use the QuickNote adding panel. So if I drag and drop any node here, those ones that are shown in this panel are not just random nodes, they actually come from statistics of usage. So we have collected a bunch of user data and we know that from after the string to date and time nodes, most of the time you're going to use the date and time difference node or the date and time based row filter. So here you have some suggestions to let you go to let you speed up to get to know all the different nodes. Otherwise you can search the nodes here, you can try to search the functionality, or even better, you can ask AI Again, if you go to this AI panel here, you can ask, for example, which node do I need to use to filter columns? Now it's just a cherry picked example, of course, but trust me, most of the times you're going to get some nice answers here and then you can just drag and drop it from there and it's the column filter in this case. Yeah.

All right, wonderful. I have to say I like the nodes that are most commonly used after the previous node. It feels like when you go on a shopping website and it's like, well, customers who bought this also.

Exactly.

Excellent. All right, so a couple of related questions around Python. One from Santiago, one from Mohammed. So basically first of all, like, can you use Python with Knime? And if you know Python already, do you need to bother with Knime?

Can you use Python? Yes. So we have, as I said in the beginning, you can include some coding snippet inside the workflow because sometimes maybe you have already something done or you know that it's gonna take you three seconds to write something in Python So why getting to know all the different nodes to do that? And it's super common. So if you look for Python, I think I don't. Maybe I don't have. Because it comes with an extension. Let me see. Maybe I can show you. Yes, I have it. You have, for example, the Python script node that you can use and it will take all the data that you have passed. So here you have the input table and you can actually write Python code using actually the columns that you have in the input table. Again, as before, if you don't know Python, but you know that you want to go in that direction, you can even ask Ki again to build some Python code for you. But if you're already familiar with Python, then this is the way to go. The Python script node there is also the R script node. We have the JavaScript node, again, the Java snippet. So we support different programming languages to include. Yeah, to include basically your code snippets inside the workflow.

Okay. And I presume the same is true if you want to write in SQL any other programming language.

Yes, exactly.

All right, excellent. Related to this different Mohammed asking, can you connect to, well, odbc. So basically, can you connect to different databases?

Yes, you can connect to databases. If you look for db, you have a bunch of DB nodes. They're a bit more advanced, so that's why we didn't show them today. But you can actually build your SQL query. And to build a query, you need to first connect to a database. And if you look for connector. Let me see if I can show you. If you actually look for connector, you can see all the things you can connect to which are not just databases, but actually like for example, file systems or different technologies, like for example, sharepoints or, or Tableau or whatever you, you want. There is probably a connector to that. And to connect to specific databases, you either there is the node or there is the DB connector that you need to kind of. You need to kind of configure manually, basically. But yes, you can connect to databases.

Okay, Sounds like most of the sort of common database types, they're all covered there. So that should cover most people's needs. All right, we're kind of past time. There are a couple of questions I want to get to. So this one from Santiago. What's the difference between the free and paid plans of now?

That's a good question. I also have a slide for that. Those are actually all the, all the things that, that I actually also wanted to, to answer the faq. But you will Find that in the slides. That's what you're pointing. There is the Knime Community Hub. So Knime analytics platform lives on your machine and everything happens on your machine. Free, open source. But at the time that you want to scale that to a team version or to an enterprise version, then there is another feature which another product which is the Knime Hub. And in this case the Knime Community Hub is basically a Knime, a Knime managed hub instance where you can collaborate on workflow projects and schedule the execution of the workflows on the browser. So basically you register you and your team, you and your team in Knime Community Hub, you create a team there and then you can upload your workflow, share your workflow with your team, share a workflow with other people that are interested with, and you can use our execution capabilities basically to schedule the execution of the workflow, for example, every day, every second day, every hour, every week, whatever you prefer. But to do this kind of automation you have to pass through the Community Hub which is basically this, this browser based instance where you can share, collaborate and execute. And execute workflows.

Okay, nice. So if you're interested in collaboration, then the paid plans may be working worth it.

Yes. Right.

So, okay, we've got 30 seconds. So quick answer from questions from Jiri. Can you do machine learning with Knime?

Nice, because I have a slide for that so it's going to be easier. It's actually, can I do data science? Yes, the answer is yes. There is a whole bunch of nodes from the different, the different machine learning algorithms already there. Or again, if you don't find the algorithm then you can create it with a code snippet. But a lot of them are already there. There is H2O integration and since few months there is also the AI integration where you can use generative AI of course, because it's everywhere and you need to be able to include that. There are some notes for that as well. So all the data science, artificial intelligence, machine learning, deep learning is also covered by some other notes.

All right, nice. That's wonderful. So with that we're well past time, we're going to wrap up. Thank you once again, Emilio. Very cool stuff. Thank you to Reese Moderating. Thank you to everyone who asked a question. Sorry we didn't get to quite all of them. Thank you for everyone for showing up. Please do come back tomorrow. We've got a great session on Azure certifications. Next Tuesday there's a session with Gaza Sky Geeks. I'm sure you know Gaza is kind of a mess at the moment, but there's still a few people in Gaza who've been making time to learn about data on Data Camp. So please do come back Tuesday to hear their story. I hope to see you all again in future sessions. All right, goodbye.