Low-Code Data Science and Analytics with KNIME

Key Takeaways

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 8 or 9 years ago, and it was only when I started putting this webinar together that I realized that actually it's not pronounced "k-nime", 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 for beginners. And yeah, very happy to to be host today for this webinar and for this actually code along or low-code along, because we're going to see that that with KNIME it's the the main point of KNIME is that you're not really coding, and we're seeing it together. If I can have my slides put up, we can um 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 the main 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're having a session of code along, where I'm building a workflow with you and you can follow along with KNIME Analytics Platform locally. And then in the end, as Richie said, we're going to have 10 minutes of Q&A, where I can answer all your questions. All right, so let's jump Let's jump into that. Let's start with KNIME Analytics Platform 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 and it's a very broad definition, I know, but really it covers everything that you that you can do. Because with Platform is you can 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 you 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 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 to access the data or maybe write some SQL queries to access the database or perform some operation R, some Java there, write some API requests if you are outsourcing your operations to a to an external provider. So, all these kind of these kind of technologies you need to more or less be able to interact with them. And also you you must be able to make them talk to each other, right? But what we do with visual programming, what we with visual workflows actually, is that you let the workflow do the interaction for you. So, what you're going to do is using a single entry point, so one single technology, in this case the visual workflow KNIME. And then under the hood is that workflow that takes care of connecting to SQL, of writing some writing to an API, or generating some or executing some code that will let you 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 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, you as I said, you don't interact with those technologies one by one, but you have one level of abstraction 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 the word workflows many many times already. I will mention it again a lot, but let me explain it more the two basic concept of KNIME Analytics Platform, which are nodes and workflows. Nodes are the let's say the 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 that have been that have been elaborated, let's say. And then you have 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 executed with with 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 it 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're 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 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 me to my career? What I can tell you is that KNIME is used cross-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 from the data specialist, so from the data analyst to data scientist within those industries, but also some business and domain experts together with some just end users that decided to to go to move on from, for example, spreadsheet 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 in the 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 to start coding together. So, what we're going to realize today, what we're going to what we're going to produce today, is is to 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 it's a bike rack, and it costs 120 euros or dollars, whatever you prefer. Or yeah, whatever you prefer. What we'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 um some of the questions that we might 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. Now, go to knime.com/download and download the package for your uh for your machine, Windows, Linux, or Mac, uh 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 uh KNAR file, again from the link in the chat. Uh 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. Um so, if you're ready, let's move to KNIME Analytics Platform and uh let's start the code-along. Let me do that like here. Uh so, when you open KNIME Analytics Platform, this is what you're going to what you're going to see for the for the first time. You are in the recent tab, you can see that here there are some welcome workflows of them 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, you can browse and look for KNIME exercise and data.knar file, which is the one that you have just downloaded from the link. Click open. And now you should see the KNIME code-along 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 uh canvas, we call it. Um and then you can start interact with it. This workflow is actually uh empty. There's um there's there are no nodes, as you can see. All you can see is that there are some there's some text, but those are just annotations that I left there to let you know what we're doing and also to remember myself what I what I have to show you, of course. Uh 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. Um so, we're going to proceed by step by step, and let's see um let's 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 um questions that we're going to try to answer um together today, and then here is the operation that we're going to produce by putting some 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 uh in KNIME, uh is to read the data. Because otherwise, we can do 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, where you basically can explore your uh all the workflows that you have in your uh 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 bike_sales.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, uh 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 how do I know that it's already configured is because if I zoom in, um the traffic light downstream down 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 this play button. And now the node is green, and we can see down in this table output part, um 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 uh 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 access access 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 product info.x uh .uh 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. Um 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 you were able to to follow. I just took the day the data and dropped 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 fir for the first questions, uh we have many ways to answer this kind of questions. If you're doing a very very shallow analysis, I highly recommend you to click on the statistics tab here. When you select the node, 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. You have the mean, uh the most common values, etc. So, without putting together many other nodes, putting together other operations, you can just have a look at the statistics tables uh statistic table for any data that you have, and um it will show up it will show you basic statistics. If you're interested in the age distribution of the customers, though, then we can uh we can create actually uh bar chart, for example, or an histogram, for example, which is able to um to show us what is the distribution uh visually. To add a new node to our workflow, what we have to do is to go to the node repository here, which is uh the second um 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 uh it needs some data. So, what are we going to create the histogram on? So, we we are missing something for that. And you can imagine that to provide the data to the histogram, we have to 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 um 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, um 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 CSV 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 um 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. Um, but it's going to make sense in a minute. So, just follow me. You can close this one. And you can see that here in the on the on the left side, you basically just have the preview of what is going to be your visualization. And on the right side, you can select the different inform the different options about this visualization. So, what you have to do to uh to calculate the country that bought the most uh that most the the the country that bought What do we have to calculate? The most products. Uh, you need to select the S category dimension. You need to select country. And you will see that this will reset somehow. Uh, so you select country at 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. Uh, and then following is Australia with 27 and so on. We just um you just have to I hope you you were able to to follow so um so far. Uh, 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. Uh, the aggregation is the sum. And basically, what it's going to do is going to is going to look at the um is going to look at the order quantity and is going to sum them up for all the different categories for all the different countries in this case. If you click okay, 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. Um, 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 um all the sales that we have that we have for different days from different countries uh for different products as well. So, if you scroll down, you can see that the products are different. Um, and then what we want to know is basically the product we want to sum up the uh the quantity that we sold for the different products and see uh which product 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 uh 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 V lookup node or the value lookup um 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. 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 uh 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 into the same row. So, just uh let's just make this this selection for now, product and product code. Click okay. 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 um 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 um 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 uh those five new columns for all the for all the rows of our table. So, uh so very simply, we have taken one information from one table and put it together into the other one. And now we can actually uh 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 no aggregate row aggregator, and drag and drop it to the workflow. Again, now the data table is uh we will take the last one, of course, the one produced by the value lookup and connect it to the row aggregator. Here. Um, the node is already 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 what we want. Um, let me open this. So, we have to select again a category column, an aggregation method, and one or more columns that we want to that we want to aggregate. What we want to do is to kind of squeeze our table basically um to select uh 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 um uh for every tube, for everything that we sell, uh we want to sum all the quantity that are in this in this uh 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. Uh, now I am 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. Uh, and I also have the sum of the quantity uh of the sales for that specific product. So, we see that this logo cap got sold 7,000 times and this bike stem got sold 265 times. Uh, because the row aggregator summed up all those um all those numbers. Uh, to answer the question, what is the best-selling product, we just need to click order quantity and this will sort our table um in descending order and we will see that the most sold product is actually a water bottle. So, uh it's not any fancy bike. Uh, it's just just a water bottle with 17,000 uh units sold and then a patch kit, a mountain tire tube, etc. And that was it for level one. Actually, I think now it's a good time to ask Rich if maybe there are some questions so far uh or maybe somebody had some problem to to follow. Because if not, I would go on with level two and three, which are a bit more exciting. All right. Yeah, we've got a a couple of uh questions. And actually, before we get to those, I just want to show a comment from the audience. You you've got a fan here. So, uh Mia is saying, "I've finished uh Emilio's course in Data Camp already. Everyone else in the audience, you're playing catch up with Mia." Uh, so uh yeah, that's good news. Um Okay. Uh, all right. So, uh questions then. Um, ding ding ding ding. Oh, actually, this this question's from Mia saying, "Um, 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 uh how you make this available for all people. Mhm. Uh this is a great question actually and I wish it was more it was more popular and I think with this kind of 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. Uh so it might be that can be um uh that it's a it's a pro and and a and a it's it's an advantage and a disadvantage. Uh so I hope that in the future we will see more and more more and more usage of KNIME around around different different department different industry even more than what we see that what we see now. Okay wonderful. Uh so there's another one from amateur. I'm 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? Uh usually no. It depends what you are maybe what you're trying to do maybe the the database is is very big I don't know. We would need to investigate the specific specific case but I I run it in different machines and usually it it kind of kind of works. If it's if it's a problem with that specific version then try to use of course the most recent version. Uh I'm in 5.3. 2 I think now. Um or send also we are we have a forum we have you can send you can ask for support and usually there are people from the community or actually KNIMEers that uh that can that can answer specific problems. Maybe 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 got problems with crashes or technical support required then go to the forum and that's where you get help. All right for this one. 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 keep they will keep coming. Um and let's move on to level two. So let's try to do something a bit more um a bit more complex slightly bit more complex not super super complex of course. Um what we want to answer now is which category let me swing which category generated the most revenues. So uh so we want to see from different categories from I think we have three categories. Uh accessory bikes and clothing which one is the one that basically generated the most 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 have 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. Um 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. Uh 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. Um so that's another way of doing it. Of course you can do the actual the the common way that you're already familiar with just whatever you prefer. Um 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 uh 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 uh 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 that we are going to introduce although it's not it's not a real programming language. It's not a specific programming language it's not Python. Um everything happens in the expression editor where we are by 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 in the right on the left you can see the different 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 sample code. Uh 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. Uh 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 have we can have a look and how it will looks 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 euros dollars whatever for this transaction more than 2700 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 it new column let's call it revenue. Like this and let's click okay. Now the node is configured and we can execute it. And if we scroll right we can see that now we have the revenues revenues column with all the with all the all the values. Um all the values filled. Now the expression node is way more powerful than this of course but for this for this operation is is more is more than enough. Uh let's try to answer the question. So which category generated the most the most revenues. We have the revenues for every transaction. Uh but again we want to aggregate and see and see for every um basically sum up all the revenues for the different categories and and see which one is the 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 that we so we be able to answer this question visually again. Let's open the configuration window. Uh if you try to execute it like this this is probably doesn't generate anything meaningful. We need to configure it. Uh 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 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 that we have for every for every transaction. If you 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. Uh if we scroll down you can put a name to your pie chart for example and call it revenues by category. You can see that this gets updated right away. Uh you can transform it to a donut chart which I like better. Um and again you have a bunch of other options that you can select for every for every visualization that you can have a look you can have a look later. So let's click okay. We have answered one more question so the level two question and we know that the category generated the the category that generated the most revenues is the bikes category as we can we can imagine being a bike shop. Uh and let's now move to level three. So let's see the most complex part of today's of today's session. Now our question become way more a bit more complex. Because what we want to answer uh 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 in the transaction involving bikes. So, all those ones need to go. All those accessory need to go. All those bike clothes needs 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 again open configuration window and add the 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 containing the the category, in this case the product category column equals bikes. So, we can actually type bikes. Let's click okay. So, product category Let me Let me 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 okay, execute, and now we can see that from 11,000 rows, we ended up with only 2,621 rows. So, I'm also repeating this 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 with only 2,621 rows. Um 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 we have performed. Um and this is actually one of the biggest difference with a spreadsheet, for example, uh because in spreadsheets, the operation and the data are kind of overlapping. So, you have one data table with the data and with the operation inside it. And in KNIME, it is different because um the operations are a bit more explicit. They are kind of um they're kind of um uh in those in those nodes, so you can always go back and see a what happened here? Let me see. The table table looked like this, and now it looked like this one. Something happened. Let me see what um 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 We want to take from this string here that have 2020-01-28, we want to be able to only extract this first part, so only the year of the of the of the date. We can do that in many 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 uh 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 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 connect it 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 is going to do this node. It's just going to parse your string and transform it into a new data data type, which is the date format, basically. So, date, guess date 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 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 okay, 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 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 uh 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 are familiar with spreadsheets, you know what pivoting is. Um so, let's try to configure this this pivoting. Let's do that together. Um 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, 2020, 2021, 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 to configure it to calculate the sum of the revenues for every country for every year. Now, if you click okay, 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 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 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 uh node. And then I can create a new visualization, which is the stacked area chart. This one. With the stacked area chart, I can configure it to show as horizontal dimension the year and as the different lines, I can show the sum of the revenues. So, let's select all of them. Let's keep them Let's keep the Australia, Canada, France, Germany, blah blah blah here in the on the right. And select sum here. Save and execute. And you can see a nice stacked area chart that basically that basically show you how the revenues went. So, in the beginning was slightly going up with the uh who was leading? I think United States was leading the revenues and Australia. Then 2021, nobody wanted a bike, and then in 2022, it went up um again. Now, um I have two more things that I want to that I want to show you. Maybe I can use one more minute. The because you might have the question of okay, and what now? So, what can I do now with this thing? So, I might I might stuck in nine. Do I have to continue in nine? And the answer is of course no. Uh you can actually drag and drop this and look for example for a writer note and you can select an Excel writer note for example. Uh with Excel writer note you can take any table. So, I I've done it with this one, but I could take any other table of my uh 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 sort somewhere, send to my colleague, uh or um I don't know pass it to pass it to another to another um um 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 uh we have read it. We have put data together, do some calculate some new features, filtered, created a pivoting table, but this is just for 2023. What if I want to do it again for uh next year for example? Uh well, the answer is that of course you don't need to recreate this thing from scratch. You just go and see uh 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'm 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 uh when you configure it to write to write somewhere else. Um 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 the concatenate node. You just put it here. Give all the tables. Give both tables. Now you have a complete tables with data from 2020 to 2024 in this case and again let's put the table here. Let's execute it again and see how the stack data chart now looks up to 2024. So, we have put together two different um uh two different um two different tables and we have run the operation again and again as new data.com. I only have one other I also have one other bonus, but I think we're running out of running out of time probably. Um which is about the KAI. We also have a AI assistant of course that is um that is used to kind of helping you generate workflow, answering you questions. For example, what is the node that I need to use or what is the expression that I need to build here? Uh well, you can do that with uh with the 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 uh and let's say that I want to to answer some some information about the seasons. Um 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 um 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 completely but this will generate a formula that I can insert in the editor and it will and this formula in this case takes the month name like this and produce a false um produce a produce a season for every different month that we might 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 click it, but at least it gives me a starting point if I don't have uh if I don't have any any clue. But of course I know that with anything that is AI generated you need to uh you need to have a second uh second pair of eyes to see uh to see if what's generated is is correct. Um all right. So, that was it actually from from my side. I can see yeah. Yeah, I see that three Thank you Emilio. Uh that was very cool stuff. Um I do like that you can do all the kind of common analytics tasks. No code needed, very simple. Uh we've got a load of audience questions. We're probably going to run over by a couple of minutes. Yeah. Um before we go to audience questions I just want to say on November 13th we have a virtual conference. So, the latest edition of the Data Camp Radar virtual conference is happening. Uh 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, we've got some sessions with managers, some sessions with practitioners. It's going to be a great session. We've got tons of great guests. So, uh yeah, please do use the QR code to uh sign up for that. All right. And with that let's quickly dive into some audience questions. So, Dan asks uh 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. Um I already have a slide for that because I knew that it would come up. And yes, the answer is yes. Uh so, everything that we have created today are single visualizations. Um but uh you could anytime put them together into a component which is another terminology that we that we use. Uh and all the visualization inside that component will become uh will become part of your dashboard. And I could quickly demonstrate it later if you want. Uh but you can do pretty cool stuff and pretty elaborate stuff. So, you can put titles, you can put uh different um uh 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 a whole dashboard standalone that is also interactive of course. Um and yeah, so the answer is yes. All right. Super. So, dashboards with KNIME that sounds like a a webinar for next year. Excellent. probably we need a new a new webinar. All right. Nice. Uh so, next question is from Pascal saying how do you know which node to use for your problem? I I see there's like lots of different nodes available. How do you know which one? Yes. Uh so, this was actually a question for a long time and we uh we were trying a lot to let to let people know all the nodes that are out there and then we realized okay, there are just so many that I don't even know all the all the nodes there. Um so, for the most common ones of course at some point they will become they will 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 quick node adding panel. So, if I if I drag and drop any node here those ones that are shown in this panel are not just random nodes. They're actually they actually come from statistics of usage. So, we have collected a bunch of user 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 base row filter. So, here we have some suggestions to let you go um uh to let you yeah, to speed up to get to know all the different nodes. Otherwise, you can search the nodes here. You can try to search the uh the the functionality or even better you can ask AI again. If you go to this uh AI panel here, you can ask for example which node uh do I need to use to filter columns. That is just a cherry picked example of course, but uh trust me most of the times it's going to you're going to get some uh some nice answers here and then you can just drag and drop it from there. And it's a column filter in this case. Yep. All right. Wonderful. I actually I like the um 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. Exactly. Excellent. All right. So, a couple of related questions around Python. One from Santiago, one from Mohammed. So, basically um first of all like can you use Python with KNIME and if you know Python already do you need to bother with KNIME? Mhm. Uh can you use Python? Yes. So, we have as I said in the beginning um you can include some coding snippet inside the workflow because sometimes maybe you have already something done or you know that it's going to take you 3 seconds to write something in Python, so why getting to know all the different nodes to do that. And that'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. Um 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 we have the input table and you can actually write Python code um to using the uh using actually the the 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 KAI again to build some Python code for you. Uh 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. Uh we have the JavaScript node. Uh again, the Java snippet. So, we support different programming languages um 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 uh any other programming language. SQL. Yes, exactly. All right. Excellent. Uh so, uh Regis this um uh different language asking um can you uh connect to uh well, ODBC So, uh basically, can you connect to different databases? Databases. Uh yes, you can connect to databases. If you look for DB, uh you have a bunch of DB nodes. They're a bit more advanced, so that's why we didn't show them today. Uh but you can actually build your SQL query, and to build the 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, um but actually like but also like, for example, um uh file um file systems or different technologies like, for example, SharePoint or um or Tableau or whatever 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 um you need to kind of configure um 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 uh that that should uh cover most people's needs. Uh all right. Uh we're kind of past time. There are a couple questions I want to get to. So, uh this one from Santiago. What's the difference between the free and paid plans of KNIME? Uh that's a good question. I have a slide for that. Uh those are actually the all the all the things that um that I actually also wanted to to answer the FAQ, but you will find it in the slides. Um that's uh what you what you're pointing there is the uh KNIME Community Hub. So, KNIME Analytics Platform lives on your machine and everything happens on your machine, free, open source. But in the at the time that you want to scale that to a team version or to an enterprise version, uh then there is uh there is another feature which come another product, which is the KNIME Hub. Uh and in this case, the KNIME Community Hub is basically a KNIME um uh a KNIME uh 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 um you and your team in KNIME Community Hub. You create a team there, and then you can upload your workflow, uh share your workflow with your team, share your 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 uh automation, you have to pass through the Community Hub, which is basically this um uh this browser-based instance, where you can share, collaborate, and execute uh and execute workflows. Okay, nice. So, if you're interested in collaboration, then the paid plans may be worth it. All right. Uh so, okay, we've got 30 seconds, so uh uh quick answer from uh question from Jerry. Uh can you do machine learning with KNIME? Yes. Nice because I have a slide for that, so it's going to be easier. Um it's actually can I do data science? Yes, the answer is yes. Uh there is a whole bunch of nodes from the different uh the different machine learning algorithms already there or again, you can put uh you can put If you don't find the algorithm, then you can create it with a with a code snippet. But a lot of them are already there. There is H2O integration. And since a 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 to be able to include that. Uh there are some nodes for that as well. So, all the data science, artificial intelligence, machine learning, deep learning is also covered uh by some other nodes. All right, nice. Uh that's wonderful. So, with that, we're well past time. We're going to wrap up. Uh thank you once again, Emilio. Very cool stuff. Thank you Teresa moderating. Thank you everyone for your questions. Sorry we didn't get quite all of them. Uh thank you 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 are still a few people in Gaza who have been making time to learn about data on DataCamp. So, please do come back Tuesday to hear their story. I hope to see you all again in future sessions. All right. Goodbye.