De Klipfolio LOOKUP functie

3 maart 2019 | 6 minuten leestijd

Een van de functies die we het vaakst gebruiken in Klipfolio is de LOOKUP. Dit is helaas niet de meest eenvoudige functie. Veel klanten komen met vragen over deze functie omdat het niet precies werkt zoals verwacht. Zeker in het begin moest ik ook even goed wennen.

Graag probeer ik je de structuur hiervan duidelijk uit te leggen. Maar voordat ik dat doe, moet ik eerst een aantal andere concepten uitleggen: GROUP, GROUPBY, SELECT & IF. Vooral het verschil tussen IF en SELECT is interessant.

GROUP

De groepeer functie zorgt er eigenlijk voor dat je van een hele serie waardes unieke waardes maakt.
Dus stel, je hebt in kolom A:A de waardes appel, appel, banaan, citroen, citroen, citroen, peer, peer en je wilt graag de unieke waardes weten, dan gebruik je GROUP(A:A) of soms GROUP(SLICE(A:A)) als je kolom nog een kop heeft.

GROUPBY

Stel, je wilt graag een bepaalde waarde (measure) berekenen per groep (dimension). Dan kun je groupby gebruiken. Stel dat de waardes behorend bij het fruit in kolom B:B de waardes 1,2,3,4,5,6,7 en 8 hebben. En die willen we sommeren. Dan gebruiken we GROUPBY in de vorm van GROUPBY(dimensie, measure, aggregatiefunctie). Als je de laatste waarde niet invult, dan zal GROUPBY automatisch sommeren.
Dus GROUPBY(A:A,B:B) levert de waardes 3 (1+2), 3 (3), 15 (4+5+6) en 15 (7+8) op. De waardes komen terug in de sorteervolgorde van de GROUP, die vervolgens automatisch alfabetisch gesorteerd wordt. 

SELECT

Een select gebruik je op het moment dat je alleen bepaalde waardes wil meenemen. Dus stel dat we in bovengenoemd voorbeeld alleen de citroenen willen, dan willen we eigenlijk programmeren: selecteer de waarde uit kolom B als wordt voldaan aan de voorwaarde dat kolom A een bepaalde waarde heeft. Oftewel: SELECT(B:B, A:A = “citroen”).

Een select statement zal records weggooien. We begonnen met 8 records, maar door het select statement blijven er maar 3 over. Dit kun je ook zien als je de formule evalueert met de bliksemschicht: als je op kolom B:B staat krijg je 8 waardes te zien, ook als je het stukje A:A = “citroen” selecteert krijg je 8 waardes te zien (true of false) maar als je de gehele formule evalueert, dan krijg je 3 waardes, namelijk 4,5 en 6.
Belangrijk is te realiseren dat beide kolommen van de select formule hetzelfde aantal records moeten hebben. Als dit niet gebeurt, dan kunnen de resultaten van deze formule onverwachte resultaten opleveren.  Het gaat bijvoorbeeld nogal eens fout bij lege cellen of NULL-waardes, vooral onderaan in een datasource.

IF

Bij een IF functie test je of een bepaalde waarde voldoet aan een criteria. Doet het dat wel, dan doe je “zus” en doet het dat niet, dan doe je “zo”. Uiteraard bepaal je zelf wat je doet. In de praktijk is “zus” of “zo” vaak het getal 0. Met andere woorden zoiets (in woorden): IF(de waarde in kolom A:A = “citroen”, B:B, 0) oftewel: als in kolom A citroen staat, pak dan de waarde uit kolom B en neem anders de waarde 0. Het resultaat is dan 0,0,0,4,5,6,0,0 

Belangrijk is te realiseren dat de 3 stukken van deze formule hetzelfde aantal records moet hebben. *) Ook het eindresultaat heeft precies dit aantal resultaten. Als dit niet zo is, dan kunnen ook hier de resultaten van deze formule onverwachte resultaten opleveren.

LOOKUP

Een LOOKUP functie heeft 3 componenten: LOOKUP(bepaalde items, waar zoeken?, geef waarde terug).

Als we dit ontleden:
In het eerste argument staan de waardes die we willen gaan opzoeken. Dat kunnen bijvoorbeeld maanden zijn, zoals “2018-07”, “2018-08”, “2018-09”, 2018-10” enz. Laten we in dit voorbeeld uitgaan van deze 4 maanden die willen zien en de rest niet. In de praktijk zijn dit vaak de waardes in de X-as van een grafiek.

In het tweede argument staat de volledige bak met waardes waarbinnen de waardes uit het eerste component kunnen staan. Dit mag in een ander format zijn, dan gaan we ze in een formule omvormen.

Het kan zijn dat er veel meer maanden in de brondata staan. Deze waardes komen dan uiteindelijk niet terug in de formule. Dat is ook wat je wil, want je hebt juist in het eerste component aangegeven wat je graag wil opzoeken.

Het kan ook zijn dat er veel minder waardes in de brondata staan. Je hebt bijvoorbeeld wel waardes van juli en augustus 2018, maar (nog) niet van september en oktober. Toch wil je wel deze maanden laten zien in je grafiek. In dat geval zal het eerste component van de lookup functie niet terugkomen in het tweede deel en daar zal de lookup formule de waarde een lege regel teruggeven.

In het derde argument leg je uit wat je precies gaat berekenen. Dus bijvoorbeeld: we zochten die 4 maanden op in een bak met data (1e component), we vonden maanden terug (2e component, in bijvoorbeeld kolom A:A), en nu willen we graag de waarde teruggeven die voorkomt in een andere kolom, bijvoorbeeld B:B.

Het aantal waardes uit argument 1 is in de meeste gevallen anders dan het aantal waardes uit argument 2. Maar het aantal waardes uit component 2 moet gelijk zijn aan het aantal waardes uit argument 3. Als dit laatste niet zo is, dan gaat er ergens wat mis.

In dit voorbeeld ga ik ervan uit dat we een grafiek willen, met op de X-as maanden en op de Y-as het aantal sessies naar een website. De X-as heet ‘Maanden’.

Er zijn nu deze mogelijkheden:

  1. De datasource waarin we zoeken heeft altijd 1 waarde per maand. Bijvoorbeeld: totaal aantal sessies per maand.
    In kolom A:A staan maanden, in kolom B:B staan het aantal sessies in die maand.

    De formule wordt:
    LOOKUP(&maanden, A:A, B:B) 
  2. De datasource waarin we zoeken heeft altijd meerdere waardes per maand. Bijvoorbeeld: aantal sessies per dag.
    In kolom C:C staat een datum, in kolom D:D staat het aantal sessies per dag

    De formule wordt
    LOOKUP(&maanden, GROUP(date_convert(C:C,”yyyyMMdd”,”yyyy-MM)),GROUPBY(date_convert(C:C,”yyyyMMdd”,”yyyy-MM),D:D)

    M.a.w: bij de tweede component een GROUP en bij het derde component een GROUPBY die op zichzelf weer 2 componenten heeft. Het eerste component hiervan is exact hetzelfde als het stukje in de GROUP van het tweede component.
  3. De datasource waarin we zoeken staat er naast maand nog een dimensie. Bijvoorbeeld: totaal aantal sessies per maand per kanaal.
    In kolom C:C staan maanden, in kolom D:D staan kanalen in kolom E:E staan aantallen.

    De formule voor het kanaal “organic” wordt dan:

    LOOKUP(&maanden, select(C:C, D:D = “organic”), select(E:E, D:D = “organic”))

    Of

    LOOKUP(&maanden, GROUP(C:C),GROUPBY(C:C,IF(D:D = “organic”,E:E,0))

    Bij de select formule selecteren we alleen de records die we willen.
    Bij de if formule selecteren we eigenlijk alles, maar, alleen als aan het criteria wordt voldaan komen er waardes terug
  4. In datasource waarin we zoeken staat naast maand nog een dimensie en de bron is in dagen en niet in maanden.
    In kolom C:C staan dagen, in kolom D:D staan kanalen en in kolom E:E staan aantallen

    De formule voor het kanaal “organic” wordt dan

    LOOKUP(&maanden, GROUP(SELECT(DATE_CONVERT(C:C,”yyyyMMdd”,”yyyy-mm”),D:D = “organic”),GROUPBY(SELECT(DATE_CONVERT(C:C,”yyyyMMdd”,”yyyy-mm”),D:D = “organic”,select(E:E, D:D = “organic”))))

    Of

    LOOKUP(&maanden, GROUP(date_convert(C:C,”yyyyMMdd”,”yyyy-MM)), GROUPBY(date_convert(C:C,”yyyyMMdd”,”yyyy-MM),IF(D:D = “organic”,E:E,0))

    Bij de select formule selecteren we alleen de records die we willen. Zowel component 2 als de beide componenten van componenten 3 krijgen de SELECT clause bij zich.

    Bij de IF formule selecteren we wel alle records, maar eigenlijk zeggen we in het laatste stukje pas dat we alleen iets met de waardes doen als aan alle criteria voldaan worden.

Wanneer gebruik je nu IF en wanneer SELECT?
Elke situatie is uniek, dus het flauwe antwoord is ‘hangt’ ervan af. Wel een paar stelregels:

  • Bij 3 keer hetzelfde select statement in de formule, is de kans op fouten groter. Dit probeer ik te vermijden. Ofwel door de functie met IF, ofwel door het op te lossen met een variabele
  • De IF formule kun je sneller uitrollen naar de andere lijnen in de grafiek, omdat je maar 1 component hoeft te veranderen
  • De IF formule vind ik zelf wat overzichtelijker en gemakkelijker te debuggen
  • Helaas kan de IF formule niet altijd. Wel met SOM, maar niet met sommige andere aggregaties zoals MIN.

Ben je met de LOOKUP functie bezig, maar kom je er niet helemaal uit? Neem vrijblijvend contact met ons op. We kunnen altijd even meekijken!

Tamara