For a couple of weeks, we have been posting on different data manipulation functionalities in Y42. Please refer back to the previous blog on this series. In this post, I’ll show two more nodes for data manipulation.
With our love for the F1 race, we created our own small company betting system. Before each race, we sent out the bets in Google Forms and collect the bets in Google Sheets and for each correct guessing, a point is rewarded. As of the current date, there have been already 9 races, which means we have 9 Google sheets for betting results.
I have already integrated the google sheet in Y42, and if you are still in the beginner's phase and have no idea, what integration means or how to import the data in Y42, please have a look at our post here.
Union
For this post, I will use only 3 of those files and show how to ‘union’ all the data into one table. To get the data from each file, we drag and drop the Input Node in the canvas and if everything went well, it should look roughly like the following:
Now to combine all the data, we use the UNION node, which is 2 steps below the Input node. Then join all the arrows to the union node and click on it for further implementation.
Before going further, I would like to point out two essential criteria, which has to be fulfilled before using the union node:
- The number of columns from each table should be the same
- The columns need to be of the same data types.
For example:
- input node “7 Monaco” has 3 columns and the first column has the datetime data type
- input node “8 Azerbaijan” has only 2 columns
- last node ”9 Canada” has 3 columns but the first column has a string data type
Then none of these tables can be combined together. Input Monaco and Azerbaijan have different numbers of columns and Monaco and Canada have different data types. You can imagine this whole process as stacking a table on top of another and in doing so, all the columns need to be the same size and type.
Now to the implementation:
There are two Union Settings options: ‘Automatic’ and ‘Manual’. If Automatic is selected, Y42 will figure out to set the columns by matching the same id. You can also choose Manual. With this option all the different columns from all the tables are shown as highlighted in the figure above. You can move around the columns if they are in the wrong position. How do you move the columns, you ask? It's Y42, everything is drag and drop😎. When all the columns are matched then, as usual, you can have a preview by clicking on Output and Save the node settings.
Date Formatter
As we know already, our first column is a datetime, i.e. this column will have the date and time when the employee has entered the responses for the bet. Let’s make two new columns, which will have date and time separately. Then let's begin it by clicking Date Formatter from the ‘Date & Time’ section.
First, let’s look at how to get the date from the timestamp column. Inside the Date Formatter node, let’s give the target column the name ‘date’ and choose the source column ‘timestamp’. For the format type we take ’DD-MM-YYYY’, which will then show the date in a day, month, and year format. If you are confused about how to format the date, then Y42 has documentation below.
Then the preview will look something like this:
Similarly, for the time column, we don’t need to add another node but we can click on the “+” symbol below the Instruction and it will create another section for time formatting. Here, as shown above, we will name ‘time’ for the target column and select timestamp as source column and in the format, we will type ‘HH:mm’, which will show 24-hour time format.
Conclusion
With these two nodes, you should be able to merge multiple tables and extract date and time from a datetime column. In upcoming posts, we will explore other rich & exciting nodes and explain how they work. In the meantime, if there are questions on these topics or any troubleshooting, don’t hesitate to contact us.
Further Links
Follow us on LinkedIn for insights into our daily work and important updates on BigQuery, Data Studio, and marketing analytics.
Subscribe to our YouTube channel for discussions on DWH, BigQuery, Looker Studio, and Google Tag Manager.
If you are interested in learning BigQuery from scratch, get access to our free BigQuery Course
Elevate your skills with Google Data Studio and BigQuery by enrolling in our Udemy course.
Need help setting up a modern, cost-efficient data warehouse or analytical dashboard? Email us at hello@datadice.io to schedule a call.
