Pivotaal datamoment #1
Van VLOOKUP naar join
Het is 2015, een nog jonge ik is druk bezig met het verwerken van honderden enquêteformulieren in een Excel-bestand om daarna met SPSS te gaan werken. Voor mijn afstudeer thesis.
Het telefoontje dat het begon
Tijdens het opschonen belde ik een maat, Martijn. Mijn vraag was simpel: als tabel 1 en tabel 2 hetzelfde nummer bevatten, kan ik dan niet een veld uit de ene in de andere terugvinden?
Antwoord: ja. VLOOKUP. Of gewoon: VERT.ZOEKEN.
Dat moment liet me voor het eerst zien dat losse tabellen niet los hoeven te blijven. En wat ik toen nog niet wist: in het datadomein heet dit principe geen VLOOKUP, maar een join. En daar gaat dit verhaal over: de good old join!
Als je VLOOKUP snapt, snap je al veel van joins
Je hebt links een tabel, rechts een tabel. Je gebruikt een gedeeld veld om ze te verbinden, dat noemen we een sleutel. Tot zover is het eigenlijk gewoon VLOOKUP.
Maar als je echt gaat engineren met data, worden de vragen al snel groter:
- Welke rijen wil ik behouden?
- Wat doe ik met records zonder match?
- Verwacht ik één match of meerdere?
- En klopt mijn model eigenlijk wel?
Precies daar verschuift het van Excel-handigheid naar echte datamodellering. Voor al die vragen heb je verschillende joins. Klik er een aan, ze staan in volgorde van complexiteit:
Klik op een join hierboven om de uitleg te lezen.
Je doel:
Je wilt alleen de rijen die in beide tabellen een match hebben.
De join:
Alleen wat links én rechts matcht, blijft over. Staat een rij maar in één van de twee? Dan valt die weg.
Bijvoorbeeld:
Je koppelt je klantenlijst aan je orderoverzicht. Klanten zonder bestelling verdwijnen. Bestellingen met een onbekend klantnummer ook.
Je gebruikt dit dus bij:
Een omzetrapport waarbij je zowel klantinfo als bestelinfo nodig hebt. Alles moet van twee kanten matchen.
Je doel:
Je wilt je linker tabel intact houden en informatie erbij zoeken.
De join:
Alles links blijft staan. Rechts vult aan waar een match bestaat. Geen match? Dan lege cellen, geen fout.
Bijvoorbeeld:
Je wilt alle klanten zien, ook degenen die nog nooit iets hebben besteld. Wie nooit besteld heeft, krijgt lege bestellingskolommen.
Je gebruikt dit dus bij:
Als jouw linker tabel je startpunt is en je aanvullende info wilt ophalen. Dit voelt het meest als VLOOKUP.
Je doel:
Je wilt de rechter tabel als je startpunt, met aanvulling vanuit links.
De join:
Hetzelfde idee als de left join, maar gespiegeld. Nu is rechts je vertrekpunt.
Bijvoorbeeld:
Je wilt alle bestellingen zien, ook als de klant niet meer in je klantenbestand staat. Elke bestelling blijft staan, ook die met een onbekend klantnummer.
Je gebruikt dit dus bij:
In de praktijk draaien de meeste mensen de tabellen om en gebruiken een left join. Maar het is goed om te weten dat deze variant bestaat.
Je doel:
Je wilt alles zien. Van beide tabellen, ook wat niet matcht.
De join:
Alles blijft zichtbaar. Van beide lijsten, of er nu een match is of niet. Lege kolommen waar de match ontbreekt.
Bijvoorbeeld:
Welke klanten hebben nooit besteld én welke bestellingen horen bij een klantnummer dat niet meer bestaat? Beide staan nu in één overzicht.
Je gebruikt dit dus bij:
Controles waarbij je niets wil missen. Precies het soort overzicht dat Finance regelmatig nodig heeft.
Je doel:
Je wilt alle mogelijke combinaties van twee lijsten genereren.
De join:
Elke rij uit lijst 1 wordt gecombineerd met elke rij uit lijst 2. Geen sleutelkolom nodig.
Bijvoorbeeld:
Je hebt 4 verkoopgebieden en 12 maanden. Een cross join geeft alle 48 combinaties. Handig als startpunt voor een budgetsjabloon of planningsmatrix.
Je gebruikt dit dus bij:
Let op: 100 rijen × 100 rijen = 10.000 rijen. Gebruik dit bewust, nooit per ongeluk.
Je doel:
Je wilt een hiërarchie of relatie zichtbaar maken die al in de tabel zit.
De join:
Je koppelt een tabel aan zichzelf. Klinkt raar, maar werkt wanneer de relatie al intern aanwezig is.
Bijvoorbeeld:
Je hebt een medewerkerstabel met een ManagerID-kolom. Dat ID verwijst naar een andere rij in dezelfde tabel. Met een self join haal je per medewerker de naam van de manager op.
Je gebruikt dit dus bij:
Kostenplaatshiërarchieën, productcategorieën met subcategorieën, of elke situatie waarbij rijen naar andere rijen in dezelfde tabel verwijzen.
Je doel:
Je wilt juist zien wat géén match heeft.
De join:
Alleen de rijen zonder match blijven over. Erg sterk voor uitzonderingsrapportages en datakwaliteit.
Bijvoorbeeld:
Welke klanten hebben nog nooit besteld? Welke budgetregels hebben nog geen werkelijke kosten? Welke facturen zijn nog niet betaald?
Je gebruikt dit dus bij:
Kwaliteitscontroles, onboarding-rapporten, of elk rapport waarbij je wil rapporteren op wat ontbreekt of nog niet gedaan is.
Je doel:
Je wilt filteren op basis van een andere tabel, zonder die kolommen toe te voegen.
De join:
Je checkt of een match bestaat, maar haalt de extra kolommen niet op. Jouw lijst blijft schoon.
Bijvoorbeeld:
Geef me alle klanten die minimaal één bestelling hebben gedaan, maar ik hoef de besteldetails niet. Resultaat: een schone klantenlijst.
Je gebruikt dit dus bij:
Belangrijk verschil met inner join: een klant met vijf bestellingen geeft bij een inner join vijf rijen. Bij een semi join altijd één. Geen dubbelingen.
Waarom dit méér is dan een trucje
Een join is technisch gezien niet het moeilijkste onderdeel van data.
Als je de bovenstaande variaties op verticaal zoeken begrijpt, ben je al op de helft. De syntax is ook niet zo lastig, zeker niet met deze pagina tot je beschikking.
Het enige wat dan nog overblijft is de vraag of je eigenlijk wel de juiste tabellen, sleutels en detailniveaus aan elkaar hangt. En precies daarom vind ik joins zo'n mooi onderwerp.
Ze lijken klein. Maar ze raken meteen aan hoe je data denkt, structureert en controleert.
Dat is ook de reden dat dit onderwerp logisch doorloopt richting mijn workshop modelleren: daar gaat het niet meer alleen over koppelen, maar over waarom een model wel of juist niet klopt.
Waarom dit voor mij een pivot moment was
Voor mij begon een belangrijk deel van mijn datareis dus niet bij Power BI, niet bij SQL, en ook niet bij een fancy dataplatform. Het begon bij een Excel-moment.
Bij het besef dat twee tabellen samen meer kunnen vertellen dan ieder voor zich. En eerlijk gezegd vind ik dat nog steeds één van de leukste dingen aan dit vak: verbanden leggen!
Dat is ook hoe ik dit soort onderwerpen aanpak op mijn site en in trainingen: rustig, logisch en zonder onnodige rookgordijnen. Meer daarover lees je in mijn werkwijze.
Heb je zo'n onderwerp waarvan je denkt: hier wil ik met mijn team of voor mezelf eens goed induiken? Dan kun je altijd even het contactformulier gebruiken.