How do I associate key-value pairs from 2 sheets?

[dtomvan]

Suppose I have 2 sheets:

• Lookup-table (key-value storage if you will) in csv
• Data sheet with keys (json, csv etc.)

And then I would want to associate the keys in the second sheet with the values from the first sheet. Ex (covid numbers exerpt and cleaned up from the Dutch health institute):
Data sheet: covid-numbers-NL-exerpt.csv
Look-up table: municipalities.csv

In this case I would want to resolve the municipality column in the data sheet to a municipality_name from the lookup table. The municipalities occur multiple times, so I couldn't use an inner join, because it doesn't preserve the original order of elements (or I'm just doing it wrong). I would want to just "add" the column to the original data, with the correct value.

What I've tried:
covid-numbers-NL-exerpt+municipalities.vdj

Thanks in advance to anyone answering.

[saulpw]

Hi @dtomvan, this is an interesting use case. I can see how key-value lookup would be super useful, and we might add a feature for something like it in the coming months. In the meantime, would the extend jointype work for you? It should preserve the original sheet type and row order and just add the column to the original data, as you want. It might be a bit of a hassle, getting the keys set up for the join and then reset afterwards, but it's worth a try for your case.

[dtomvan]

Yes, that works. But the column names get changed as well. So your right, it is a bit of a hassle, but it works. Thanks!

[aborruso]

@dtomvan I'm not sure I understand. Want to get this?

[dtomvan]

Yup, that seems correct.

[aborruso]

Yup, that seems correct.

@dtomvan It's simply a join. I have created a video that shows how to do it https://youtu.be/3R_tGWWNK-Y

I inserted pauses to highlight some of the keys I pressed

[dtomvan]

Oh, alright, an outer join works as well. Thanks!