LookUps in Coda.io
Afgelopen kerstvakantie kreeg ik te maken met twee uitdagingen. De eerste was dat ik de toe- en afname van mijn gewicht beter willen kunnen opvolgen. Twee tabellen helpen me: de eerste met de dagelijkse meetresultaten en de ander met de doelstellingen voor die dag. In deze tweede tabel werk ik met doelstellingen per ongeveer 45 dagen. De tweede uitdaging is deplanning van de 13 weken durende zomervakantie voor onze kinderen qua kampen. Ook hier zijn er twee tabellen. De eerste waarin de zomervakantie staat en de tweede bevat de kampen per kind van dag x tot dag y voor zoon L en / of N
In deze blog leg ik uit hoe je deze twee problemen kan oplossen via een LookUp. Het is een uitleg voor gebruikers met enige ervaring.
Een LookUp is een Filter variant. Dat is belangrijk om in gedachten te houden als je ermee werkt. Voor de leesbaarheid noteer ik beiden op dezelfde manier:
TabelNaam.Lookup(kolom,MatchValue)TabelNaam.Filter(voorwaarden)
Opvolging gewicht
Ik maak gebruik van twee tabellen. In de eerste tabel noteer ik mijn gewicht en in de tweede tabel bepaal ik mijn doelstellingen. Voor de duidelijkheid, dit zijn voor deze doelstelling opgebouwde tabellen. Degene die ik persoonlijk gebruik bevatten andere informatie en ook meer functies.
De tabel waarin ik mijn gewicht noteer is eenvoudig en bevat voor deze oefening de datum, het gewicht en de target (in kg). De tweede tabel heb ik vergelijkbare kolommen: streefgewicht en de datum dat ik dat resultaat bereikt wil hebben. De overige informatie helpt om de doelen helder te krijgen. De logica achter deze extra kolommen heb ik besproken in een ander artikel over previousRow.
Vraag:
Hoe krijgen we per datum het juiste gewicht in de tabel: ‘gewicht per dag’?
Een LookUp met een Filter
Gegevens uit een andere tabel ophalen doe je met een LookUp. Deze functie kent drie bouwstenen. Er zijn twee vaste waarden zijn, waarden die je via een formule niet kan wijzigen. Dat is ten eerste de Tabel waarin je de opzoeking doet en ten tweede de kolom waarbinnen je zoekt. Het laatste element is de de MatchValue om mee te variëren.
Dit is de formule die werkt en stap voor stap leg ik uit hoe de MatchValue is opgebouwd.
[Gewenst Gewicht].Lookup(Doeldatum,[Gewenst Gewicht].Filter(Doeldatum>=thisRow.Datum).Doeldatum.First()).[Doel in KG]In de tabel [Gewicht per dag] is er elke dag een nieuwe datum, deze wordt genoteerd in de kolom [datum]. Echter de datum die ik zoek bevindt zich in een andere tabel te weten[Gewenst Gewicht] en dan ergens in de kolom [Doel Datum]. De vraag is hoe vind ik de datum die past bij vandaag en wat staat er dan op die rij als streefgewicht bij [Doel in KG]? Immers in de Tabel [Gewenst Gewicht] is er niet expliciet voor elke dag een gewicht voorzien. We dienen de datum te vinden via een vergelijking, een formule.
Het begint er mee dat ik in de tabel [Gewenst Gewicht] een Sort() heb toegepast op de datum zodat de oudste altijd bovenaan staat. Voeg ik dan rij toe met een datum, dan komt die automatisch juist te staan, dat wil zeggen qua datum op de juiste plaats. Dat is belangrijk omdat we ‘vandaag’ als uitgangspunt nemen en ‘vandaag’ is een relatief gegeven, ze is elke dag anders. Echter als ze er een rij verkeerd staat, dan gaat de opzoeking fout. Dit is stap één.
We gaan vervolgens verder met het opbouwen van een filter. Dat doen we op de klassieke wijze. Eerst noteren we de naam van de tabel, daarna schrijven we de functie Filter, vervolgens binnen de haakjes de voorwaarden om dan buiten het laatste haakje af te sluiten met wat we willen tonen.
[Gewenst Gewicht].Filter(Doeldatum>=thisRow.Datum).Doeldatum.First()We zeggen dat we een datum zoeken die groter of gelijk is aan vandaag en dat we de eerste willen hebben die we tegenkomen. Groter of gelijk aan vandaag omdat het doelgewicht doorgaans in de toekomst ligt en slechts één keer samenvalt met de doeldatum. Het is al met al een eenvoudige filter.
Vanaf hier kunnen we in stap drie de lookUp uitbouwen door twee dingen te doen ter afronding.
- we gebruiken de Filter als de MatchValue in de kolom datum. Logischerwijs dient de filter te vragen om een datum (doeldatum), we zoeken immers in een kolom met datums.
- we vertellen de lookUp welke uitkomst we willen en dat is het doelgewicht: [Doel in KG]
En zo hebben we een slimme LookUp waarbij de MatchValue een filter is in de vastgestelde kolom (datum) en de uitkomst een doelgewicht in KG. Deze oplossing werkt omdat de beide tabellen op een vergelijkbare manier opgebouwd zijn. Ze hebben beiden een kolom datum en waardoor de waarden kunnen worden vergeleken.
Voor het volgende probleem werken we anders. We gebuiken Sequence() gecombineerd met een FormulaMap(). Dit komt omdat we hier twee tabellen hebben die anders opgebouwd zijn. Dat vraagt wat creativiteit zodat we de LookUp kunnen laten werken op een kolom in de brontabel die de informatie bevat die we nodig hebben in de doeltabel, ook al wordt ze zo niet expliciet ingegeven.
Zomerkampen per week per kind
Er is nog een andere vorm van LookUp die we met name in de context van planning tegenkomen. We hebben opnieuw twee tabellen. Eén met daarin de vakanties van de kinderen. Deze tabel heeft naast een kolom ‘activiteit’ kolommen als ‘van’ en ‘tot’ en het aantal dagen naast nog wat kolommen voor de visuele herkenbaarheid (de namen van de dagen).
Een deel van die tabel vind je hieronder met direct een belangrijk element van de oplossing van het probleem dat zich stelt: hoe breng je informatie bij elkaar via een LookUp als twee tabellen initieel niet beschikken over vergelijkbare kolommen? Oplossing : je voegt dan een kolom toe met daarin gegevens op basis van andere kolommen die dezelfde informatie bevat als de kolom waarmee je werkt in de andere tabel.
We hebben hier de dagen nodig per type vakantie zoals bijvoorbeeld een kinderkamp aan zee of een kamp in Italië. Het eerste kamp vertrekt al op de 30ste juni en duurt tien dagen. Vandaar dat je in de screenshot 4 data ziet met daaronder nog 6 dagen. We gebruiken onderstaande formule:
Sequence(thisRow.van,thisRow.tot).FormulaMap(CurrentValue.ToDate())Via deze formule krijgen we per vakantie een lijst met het aantal dagen en de datums zelf. Een FormulaMap evalueert immers ieder item in de lijst waarop ze van toepassing is. Deze lijst wordt gevormd middels de functie ‘Sequence()’ waarbij je een begin en een eindwaarde opgeeft. Deze lijst bevat 10 items in de eerste rij omdat de vakantie 10 dagen duurt. Vervolgens vragen we voor elk item uit deze lijst via de CurrentValue wat van dit item de datum is. Je kunt hier evengoed ook de weeknaam kiezen als je dat wil. In deze context is de datum van belang omdat de tweede tabel specifiek gaat over de zomervakantie.
De zomervakantie
Dit is een tabel die per dag aangeeft waar de kinderen zijn. En ziet er uit als onderstaande :
De aardigheid is dat we per dag nu kunnen zien waar de kinderen zijn. Het is daarmee visueel een controle tabel om de planning te optimaliseren. Via een filter kan ik zelfs alleen de rijen (dagen) tonen die nog komen gaan (inclusief die van vandaag) . De formule die ons het vakantietype toont is:
[Kampen Boys].Lookup(TussenDagen,thisRow.Datum)we doen een Lookup in de kolom TussenDagen en als we daarin de datum vinden uit deze rij, via thisRow.datum dan krijgen we automatisch de waarde aangeboden uit de display column van de betreffende rij en hier is dat de vakantienaam. En met dat we twee jongens hebbend die ieder hun eigen programma volgen, zien we per dag waar ze zijn en zag ik verder op dat er nog dagen zijn waarvoor de invulling mist. Deze blog schrijf ik half januari en daarmee is er nog wel wat tijd om een leuke invulling te fixen.
Conclusie
We hebben twee keer gebruik gemaakt van een Lookup. In het eerste voorbeeld hadden de tabellen een vergelijkbare opbouw. Beiden beschikten over een kolom datum. Een filter om de datum te vinden als MatchValue volstaat dan. In het tweede voorbeeld werd de behoefte aan een nieuwe kolom duidelijk als voorwaarde om een kolom te hebben waarbinnen de MatchValue te vinden zou zijn. Deze kolom hebben we gebouwd gebruik te maken van Sequence() en FormulaMap(). Dit is een elegante oplossing met een zekere complexiteit met dat je de dagen tussen een start- en einddatum dient te lezen als lijst en je de vakantiedag één voor één dient te evalueren om te weten welke datum het betreft per dag.
Mijn naam is Christiaan Huizer en ik ben eigenaar van Huizer Automatisatie. In mijn rol als bedrijfsadviseur ben ik actief in het midden- en kleinbedrijf in Wallonië, Brussel, Vlaanderen en Nederland. Mijn klanten vragen me vooral om hun bedrijf voordelig te vereenvoudigen en versnellen. Ik maak daarbij gebruik van tools als AirTable, Coda, Zapier, MailJet en PaperForm.
