innerjoin in Numbers?

Question

Level 1

46 points

innerjoin in Numbers?

I can't figure out whether the feature is absent, or just obscure. No online help or presence in User Manual. Is an innerjoin possible in Numbers?

iMac 21.5″, macOS 10.15

Posted on Aug 28, 2021 6:24 PM

Reply

Answer 1

Top-ranking reply

Badunit

Level 7

30,732 points

Aug 29, 2021 6:50 PM in response to jkr98116

Everything in the two tables is typed in except for column C of the Facts table. It has the formula

=XLOOKUP(A,Individuals::A,Individuals::B,"Not Found",0)

put that in every row of column C

If you want to lock in those results (replace the formulas with their results), you can Copy column C then Paste Formula Results back to column C. The formulas will be gone and it will be as if you typed that data into column C. Or just leave it as formulas, like a relational database.

Reply

Answer 2

Badunit

Level 7

30,732 points

Aug 29, 2021 7:14 AM in response to jkr98116

I am taking "inner join" as where two sets of data are compared and the "inner join" is the set of the values common to both sets. Or, in a Venn Diagram, the values in the overlapping part of the circles.

This is not a feature of Numbers but you can do it with formulas. There are numerous choices of formulas.

I originally posted a few different options but it got so long. Here is maybe the best for what I think you want.

Table 1 has your two sets of data

Formula in cell 2 of Table 2 is =IF(COUNTIF(Table 1::A,Table 1::B2)>0,Table 1::B2,"")

fill down to complete the column

The results will not be at the top like in the screenshot. Sort on that column to put the results at the top.

You could do it all in Table 1 but, when you sort, it will sort your data columns, too. A row is like a record in a database. A row moves as one unit when a table is sorted.

Reply

Answer 3

jkr98116 Author

Level 1

46 points

Aug 29, 2021 5:23 PM in response to Badunit

I appreciate the effort. Regrettably, it misses the mark.

INNERJOIN is a Structured Query Language (SQL) function. SQL is the language for using relational databases, such as SQL SERVER, or MYSQL, or SQLite. A spreadsheet is not a database, but with care can be used as one for limited purposes.

In the past, I've found it supported in Excel, but that turns out to depend on an extension I don't have access to.

I'm disappointed there isn't support in Numbers. I guess I'll have to work up a relational database for my data. I was trying to avoid the considerable trouble that involves.

Thanks anyway.

Reply

Answer 4

Badunit

Level 7

30,732 points

Aug 29, 2021 5:42 PM in response to jkr98116

The formula I provided will find and list the common elements from two sets of data. You say it misses the mark but didn't say what that mark is so it's kinda hard to come up with something else. What are you trying to accomplish? What are you starting with and what is the end result you are looking for?

Reply

Answer 5

jkr98116 Author

Level 1

46 points

Aug 29, 2021 6:09 PM in response to Badunit

Fair enough. I doubt it will make sense to try to recreate in Numbers, but for what it's worth:

I have tables in two sheets. They share a unique identifier. One sheet (Facts) has many entries for each identifier, the other (Individuals) has only one for each identifier. I want to look up, if you will, one other field from Individuals to attach to each row with the matching identfier in Facts, and add that field to Facts.

Reply

Answer 6

jkr98116 Author

Level 1

46 points

Aug 30, 2021 12:22 PM in response to Badunit

Awesome. When I started typing =XLOOKUP, the formula editor in Numbers took over and produced a slightly differing syntax as the result:

XLOOKUP(ID,Individuals::A,Ahnentafel,[if-not-found],[match-type])

where the items in square brackets are actually in ovals produced by the editor and the other items were picked as the respective spreadsheet columns. This took a little tinkering to figure out, but the result is exactly what I was looking for. Now for each of > 15,000 facts I have an associated Ahnentafel number. I copied and Pasted Formula Results to a new column, then in a next new column performed DEC2BIN on that, and in a next new column performed LEN on that, resulting in a column of how many generations back the individual is from me (due to a property of the Ahnentafel number), which is what I was actually seeking. The latter two columns are calculations provided by functions of Numbers, which would not be available in a relational database, and exhibiting an advantage of sticking to the spreadsheet.

An issue emerged that DEC2BIN throws an error for too large a number (e.g. 64163379857653800), so that I can't calculate generations beyond 54 back, but they (inherited from other sources) are totally implausible anyway.

What can I say - you Bad! ;+}

Thanks so much.

Reply