Hi Everyone,
I have two tables with one common column with the date in form of month and day (eg Jan 1). The first table has in addition user accounts. The second doesn’t have user account but has the month, day and day of the week (eg Monday). How does one create one table that combines both user account, month, date and day of the week? The idea is to create a table of failed user logins on weekends Saturday and Sunday)
Hi @Steve_Bunde. Welcome to the KNIME Community.
It would be easier to help if we could see an uploaded a simple example of your data as then things like column names would be clearer but going on what you have described, that there is a single common column containing a date in Mon dd format, hopefully this example will make it clear.
You can use a joiner node to join the two tables:
The join settings would be this:
I would suggest in my example above ticking “left unmatched rows” so that if a date isn’t present you would still have the “user record” but with a missing value for the dayname.
In the column settings you would select only the dayname to be included from the “right” table (bottom table attached to the Joiner), so as not to other column(s).
If you are using KNIME 5.x and do not see the “joiner node”, make sure you have KNIME set to show all nodes rather than just “starter nodes”
As you are looking up details using a single column value, an alternative to this, in KNIME 5.x, is the Value Lookup node
Thanks for the response @takbb will surely give it a try. I have attached the sample data for this exercise. I have attached the sample data for reference.
Hi @Steve_Bunde , I can immediately see one potential problem. Since your calendar data has a year in it, and your user data doesn’t, it is presumably possible that at some point, you will have the wrong year, but ignoring that, I think the suggestion above should work for you.
If your user data did also have a year associated with it, then it would be possible to create a complete date and thus determine the day of the week using other date-oriented nodes, such as “Date&Time to String” and “Extract Date&Time Fields” without having to perform the lookup in another table.
Sorry, I had shared a wrong output. Attached is the right one
I have tried the two solutions above and none seem to work. See attached screenshots.
I think the situations need further clarifications. which seem to be where the problem lies. I describe a scenario below;
User A has 4 failed logins on Sunday, 19 May at 3.10am, 3.14am, 3.17am and 3.21am. There is an extract listing these logins in the format below;
UserAccount | EventDescription | Date | Time
User A | Failed Login | 19 May | 3.10am
User A | Failed Login | 19 May | 3.14am
User A | Failed Login | 19 May | 3.17am
User A | Failed Login | 19 May | 3.21am
I have another table that has the days of the week (which are not included here. The format is as below:
Date | DayOfWeek
17 May | Friday
18 May | Saturday
19 May | Sunday
I need an output resembling below:
UserAccount | EventDescription | Date | DayOfWeek
User A | Failed Login | 19 May | Sunday
User A | Failed Login | 19 May | Sunday
User A | Failed Login | 19 May | Sunday
User A | Failed Login | 19 May | Sunday
Ofc ourse there may be other failed logins from User B, User D etc on other weekends which I need captured in the format below.
How can I achieve my goal?
Thanks in advance
Steve B
Hi @Steve_Bunde , the solutions that I posted should work for this. Are you sure you don’t have additional spaces around the Month and day, or something else causing them to not match?
Can you post screenshot of your joiner or value lookup config, or better still upload a small sample demo workflow with data because if your data is as you have said, I don’t see why it wouldn’t work. It should be a very simple join.
Hi @takbb, you were right. There was a space before the dates. It now works perfectly, thanks.
That’s great @Steve_Bunde. Glad it’s working.
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.
