Wie met databronnen en software van Amerikaanse makelij te maken heeft – van Microsoft Office tot vi-UNIX – loopt vanzelf tegen het probleem van de datumnotatie aan. Soms is het makkelijk aan te passen, soms niet. Zo kan een bepaalde functie hardnekkig terugkeren naar de ‘oer’-datumnotatie mm-dd-yyyy. Hoe zit dit met Tableau software? Is het mogelijk om Amerikaanse en Europese datum-formats samen te voegen? Hieronder vindt u de werkwijze aan de hand van Tableau Prep Builder.
Dit is het tweede deel van het blog, het eerste deel is hier te vinden. Als u geen Tableau Prep gebruikt of het liever oplost in Tableau Desktop is de oplossing in dat blog te vinden.
De Amerikaanse en Europese datumnotatie of datum formats
Stel een bedrijf dat hout importeert uit verschillende EU-landen en de VS ontvangt de onderstaande twee bestanden met transportgegevens. De linker tabel bevat gegevens met de Europese datumnotatie (dag-maand-jaar of dd-mm-yyyy). De rechter tabel de Amerikaanse notatie (maand-dag-jaar of mm-dd-yyyy). Voor het totaaloverzicht moet dit in één worksheet komen zonder dat er in het worksheet zelf met calculated fields gepuzzeld moet worden. (Het is namelijk handig als elke Tableau-gebruiker dezelfde kolomnamen gebruikt en in het algemeen dezelfde gegevens ontvangt).
Oplossing Tableau Prep – A
Om twee kolommen via Tableau Prep samen te voegen hoeft u alleen maar de twee tabbladen uit de bron het canvas in te slepen. Vervolgens sleept u één van de twee bronnen op de ander en kies dan voor union.
Het onderstaande beeld verschijnt hierop, in de Profile Pane (de onderste helft) kunt u de kolommen samenvoegen. Selecteer met Ctrl (op Mac Command) de twee kolommen. Klik op de drie stippen (rechtsboven in de geselecteerde kolom) en klik op ‘Merge Fields’ zoals onderstaand:
Maar wat te doen met ‘Shipping date’ , een string value, en een Verzenddatum, een datum. Klik op ‘Abc’ linksboven in de kolom, hierna er komt een uitklapmenu. Selecteer ‘Date’, vervolgens vormt Tableau Prep de Amerikaanse notatie automatisch om naar de Europese. Vervolgens kan de bovenstaande merge-functie toegepast worden. Daaruit volgt het onderstaande resultaat:
Oplossing Tableau Prep – B
Maar wat als de bovenstaande oplossing niet werkt omdat de data te rommelig is? Met een calculated field kunt u dit makkelijk doen. Selecteer de drie stippen, en klik op het uitklapmenu op ‘Create Calculated Field’ en vervolgens op ‘Custom Calculation’.
U kunt de volgende formule invoeren:
De formule bestaat uit de volgende delen, als voorbeeld pakken we de datum uit de eerste regel: 12-06-2021 op zijn Amerikaans. Oftewel: 6 december 2021 volgens onze jaartelling.
- I. DATE(…), vormt de informatie tussen de haakjes om naar een datum zonder tijdsaanduiding. De functie is letterlijk: DATE(expression);
- II. DATEPARSE(…), is letterlijk: DATEPARSE(format,string). De functie verandert een string naar een specifiek datum-format, bijvoorbeeld ‘dd-MM-yyyy’ uit het voorbeeld zet het Amerikaanse ‘6 december 2021′ om naar het Europese ’06-12-2021’.
- III. MID([Shipping date],4,2) zoekt de tekens in een string aan de hand van een opgegeven startpunt en lengte. De functie is letterlijk: MID(string, start, length (length is optioneel). In de voorbeeldregel (12-06-2021) is het vierde teken ‘0’ en is de lengte van de totale tekst twee tekens, daarom is het resultaat ’06’.
- IV. LEFT([Shipping date],2), zoekt de tekens in een string vanaf links en telt het aantal tekens aan de hand van een ingevoerd getal. De functie is letterlijk: LEFT(string, num_chars). In de voorbeeldregel (12-06-2021), daarom is het eindresultaat ’12’.
- V. RIGHT([Shipping date],4) zoekt de tekens in een string vanaf rechts en telt het aantal tekens aan de hand van het ingevoerde getal. De functie is letterlijk: RIGHT(string, num_chars).
De delen “+’-‘+” zijn bedoeld om de scheidingstekens tussen de datum-getallen toe te voegen.
Vervolgens heeft u een calculated field nodig om de kolommen ‘Received date EU’ en ‘Ontvangstdatum’ samen te voegen, open het calculated field:
Type vervolgens de onderstaande formule in:
- DATE(DATEPARSE(“yyyy-MM-dd”,IFNULL(STR([Received date EU]),STR([Ontvangstdatum]))))
Wat gebeurt hier? De functies DATE en DATEPARSE zijn al toegelicht, maar wat doen de functies IFNULL en STR? Met IFNULL(expr 1, expr2) zorgt de formule ervoor dat bij een lege waarde (NULL) in expr1 de waarde uit expr2 gebruikt wordt. In de afbeeldingen is te zien dat de er geen overlap zit in de waarden, want door de union-mismatch is er een NULL in de cel als de kolomnaam niet overeenkomt. Door IFNULL worden de twee kolommen die in expr1 en expr2 genoemd worden samengevoegd.
STR is nodig om met IFNULL te werken, omdat het datum format niet werkt met die functie. De functie STR maakt van een waarde een string value.
Als de halfvolle (of halflege) kolommen verborgen zijn ziet het eindresultaat er als volgt uit:
Bronnen en extra informatie
Een korte uitleg over Union is hier te vinden of op de site van Tableau zelf of een compactere uitleg hier.
Voor meer uitleg over de string-functions, zie de Tableau-site of deze Youtube filmpjes over de functie ‘MID’ en de functies LEFT en RIGHT.
Wilt u meer weten over de functies DATE en DATEPARSE, klik dan hier voor de Tableau-site of hier voor een korter overzicht.
Dit blog is (deels) gebaseerd op deze post op community.tableau.com van Michael Ye.
( Photo by Sinjin Thomas on Unsplash)