Two unique identifiers?

KNIME Analytics Platform
1

Hello all,

Using the Joiner node, I can match by a product ID number - but is there a way to also match by date?

For example, I have two data sets that look a bit like the below:

Data Set One:
ID: 0001, URL: example.com/abc, Date: 01/01/19
ID: 0001, URL: example.com/efg, Date: 05/01/19
ID: 0002, URL: example.com/abc, Date: 01/01/19
ID: 0002, URL: example.com/xyz, Date: 02/01/19
ID: 0002, URL: example.com/efg, Date: 03/01/19
ID: 0003, URL: example.com/lmo, Date: 05/01/19
ID: 0003, URL: example.com/uvw, Date: 03/01/19

Data Set Two:
ID: 0001, Name: abc, Date: 01/01/19
ID: 0001, Name: efg, Date: 09/01/19
ID: 0002, Name: abc, Date: 01/01/19
ID: 0002, Name: xyz, Date: 04/01/19
ID: 0002, Name: efg, Date: 03/01/19
ID: 0003, Name: lmo, Date: 07/01/19
ID: 0003, Name: uvw, Date: 03/01/19

But I need them to be matched by both ID and by date where it’s applicable. Is there a way to do this?

2

Hi,

Untitled
Untitled553×624 58.2 KB

Is that clear?

Best,
Armin

2 Likes
3

Ah, yes it is - thank you!

2 Likes
4

Hi Armingrudd,

I get an error when I try to join it with the date also - possibly because not all the dates match.

Does the option above work as a hierarchy, as in first join the IDs, and then join the dates afterwards - or does it try to do all at once?

5

There are two options:

Match all of the following: This is AND between the joining columns
Match any of the following: This is OR between the joining columns

Untitled
Untitled521×626 59.3 KB

What is the difference? whether it checks the first column then the second or both at once the logic says they both should match when you have selected the first option (Match all of the following).

What error do you get?

6

Ah, I guess I worry that it will match on some dates without matching on IDs. So perhaps there will be a case that data is pulled together by date only. I’m not sure I am explaining this so well.

Basically what I am looking to do, is have it so that the IDs are all matched, and then if dates match within the IDs then they would be matched also - does that make sense?

7

For now it doesn’t make sense to me.

So let’s follow your logic step by step:
First, the tables should be joined based on the IDs, so we have different dates in a single joined row. Each instance with the same ID in the second table will create a new row per each row in the first table. For example, for the ID = 0001 in your sample datasets there will be 4 rows. The first row from the first table joins with the first 2 rows from the second table and the same for the second row from the first table so finally we have 4 rows for ID = 0001.
Untitled

Then you want this:

In the joined table where I provided its image, there are 2 cases:
Dates match as well.
Dates do not match.

If you want those rows in which dates match as well, then the approach where we joined tables based on both ID and Date at once is the solution.

What output are you looking for exactly?

Armin

8

Thank you Armin,

I think to be honest I have gotten myself deeper than what I am capable of, I think I should perhaps look to hire some help.

9

Hi there @SteveO ,

If you haven’t given up on this matter you can write down what is your desired output of the two data sets you from your first post and I’m sure someone will try to help you :wink:

Br,
Ivan

10

Thanks Ipazin,

I actually tried to write it down, but I think I am not capable of explaining it - perhaps because I don’t understand it as well as I thought It did, myself. Also, I don’t want to keep taking up too much of you guys’ time.

11

ok.
If you figure it out feel free to try explaining it to us :slight_smile:
Br,
Ivan

2 Likes
12

Hi all,

i would solve it like this. Maybe this helps.

Br,
Hermann

screenshot Two_unique_identifiers.knwf (14.7 KB)

1 Like
13

Amazing, thank you - I will try to get my head around that!

14

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.