Join für Datumsbereiche | Tipps Tuesday #010

[RolandSchubert]

Für viele gehört es in der täglichen Arbeit zu den häufigsten Aufgaben, Daten aus zwei Tabellen miteinander zu verknüpfen. Mal sollen Umsätze durch Informationen aus dem Artikelstamm ergänzt werden, mal brauchen wir zu einer Liste mit Konten und gebuchten Werten die entsprechenden P&L-Positionen oder wir wollen in einer Auflistung mit Personalnummer und Arbeitsstunden auch die Namen der Mitarbeiter sehen. In all diesen Fällen gibt es in den beteiligten Tabellen jeweils mindestens ein Feld, das exakt übereinstimmt und über das die Werte zugeordnet werden können. In Alteryx kein Problem, schließlich gibt es eine Reihe von Tools dafür (Join, Join Multiple oder Find Replace).

Was aber ist zu tun, wenn wir nicht diese exakte Entsprechung haben, also nicht in einer Tabelle die Absatzmenge für den 17.01.2021 finden und in einer zweiten Tabelle den Preis für genau diesen Tag?  Wenn es zwar auf der einen Seite ein konkretes Datum gibt, auf der anderen aber ein Zeitraum angegeben ist und die Verknüpfung darüber hergestellt werden kann, dass das Datum in diesem Zeitraum liegt? Mit genau diesem Problem beschäftigen wir uns in diesem Tuesday Tipp.

Nehmen wir einmal an, unsere Aufgabe ist es, die Umsätze von einigen Aktionen zu vergleichen. Dazu haben wir eine Datei mit Transaktionsdaten zur Verfügung, die jeweils Datum und einzelne Umsätze enthält:

Die Aktionen und Aktionszeiträume finden wir in einer zweiten Datei:

Die Bedingung für eine Zuordnung ist jetzt, dass das Datum aus der ersten Tabelle im Zeitraum liegt, der durch die Felder „Von“ und „Bis“ festgelegt wird. Das JOIN Tool verlangt, dass die Felder, über die verknüpft werden soll, exakt übereinstimmen – also was jetzt?

Natürlich lässt sich dieses Problem trotzdem einfach lösen, wir haben sogar die Auswahl zwischen verschiedenen Lösungen. Schauen wir uns die Varianten doch mal an unserem Beispiel an. Unabhängig von der Methode müssen wir natürlich die Daten zunächst in Alteryx laden; dazu nutzen wir jeweils ein Input Data Tool:

Variante 1: Generate Rows + Join

Im konkreten Fall können wir ja nicht einfach das Join Tool nutzen, weil die zweite Tabelle nur eine von-bis-Angabe enthält und kein konkretes Datum. Also schaffen wir doch einfach die Voraussetzungen! Dazu wandeln wir - sofern erforderlich - zunächst die Datumsfelder in den Datentyp „Date“ um. Das können wir mit DateTime Tools erledigen, ich nutze meist ein Formula Tool und die Funktion DateTimeParse([Datum], `%d.%m.%Y`).

Dann setzen wir ein Generate Rows Tool ein, um für jeden Tag im Aktionszeitraum einen Datensatz zu erzeugen. Die Felder [Start] ([Von] in Date umgewandelt) und [Ende] ([Bis] in Date umgewandelt) steuern dabei den Bereich, der abgedeckt werden soll, mit DateTimeAdd können wir jeweils einen Tag hinzuaddieren, bis das Ende des Zeitraums erreicht ist.

Aus einem Datensatz für den Aktionszeitraum werden in unserem Beispiel sieben, also je Tag einer, und jeder dieser Datensätze enthält ein genaues Datum:

Nun können wir mit dem Join Tool arbeiten, um die Aktionen den Umsätzen zuzuordnen – wir haben jetzt keine Bereich mehr, sondern ein konkretes Datum, das wir als Join Feld nutzen können.

Hier bekommen wir die zugeordneten Datensätze über den „J“ Output Anchor, alle Daten außerhalb der Aktionszeiträume landen im „L“-Output, Tage ohne Umsatz im Aktionszeitraum finden wir über den „R“-Output.

Der komplette Ablauf sieht dann so aus:

Der große Vorteil dieser Variante ist, dass wir auch mehrere Felder für die Verknüpfung nutzen können - wenn wir zum Beispiel neben dem Datum, das in einem bestimmten Zeitraum liegen soll, auch noch eine Verknüpfung über eine Artikelnummer brauchen, wäre das ein geeigneter Ansatz.

Schwierig wird es allerdings, wenn wir zwar nur ein Feld für die Verknüpfung brauchen, das Datum und Zeitraum aber mit Zeitangabe (Stunde, Minute, ...) definiert werden. Natürlich können wir mit den Generate Rows Tool auch einen Datensatz je Stunde oder Minute erzeugen, aber hier ist ein anderer Ansatz sinnvoller.

Variante 2: Append Fields + Filter

Während wir bei unserem ersten Ansatz die Voraussetzungen geschaffen haben, um das Join Tool anzuwenden, gehen wir hier einen anderen Weg. Wieder müssen wir zunächst die Datumsfelder ([Datum], [Von], [Bis]) in den Datentyp „Date“ umwandeln. An unserer Grundproblematik hat sich aber immer noch nichts geändert, wir haben immer noch ein konkretes Datum in den Transaktionsdaten und einen Datumsbereich in der Liste der Aktionszeiträume. Jetzt nutzen wir ein Append Fields Tool und ordnen damit jedem Datensatz aus den Transaktionsdaten alle Datensätze aus der Tabelle mit den Aktionen zu.

In unserem Beispiel werden aus jedem Datensatz der Transaktionsdatentabelle vier Datensätze mit den unterschiedlichen Zeiträumen. Bleibt nur noch, den „richtigen“ Datensatz zu finden, also den, bei dem das Datum ([Date]) im Aktionszeitraum ([Start] – [Ende]) liegt. Um diese Werte vergleichen zu können, mussten wir vorher auch in den Datentyp „Date“ umwandeln – der Vergleich funktioniert mit Textdaten natürlich nicht.

Der „T“ Output Anchor liefert uns jetzt die korrekt zugeordneten Datensätze. Fertig.

Auch hier einmal der Ablauf im Überblick:

Bei diesem Ansatz haben wir den Vorteil, dass er unabhängig von der "Genauigkeit" der Datumsbereiche anwendbar ist - also völlig egal, ob Tage, Stunden, Minuten oder Sekunden angegeben sind. Auch für eine Verknüpfung über Wertbereiche kann der Ansatz genutzt werden (Wert von 1200 in Tabelle 1 liegt im Wertebereich zwischen 1000 und 2000 in Tabelle 2).

Falls zur Verknüpfung mehrere Felder gebraucht werden, müssen wir statt Append Fields wieder ein Join Tool benutzen (da werden dann nur die zusätzlichen Felder als Join Field angegeben).

Variante 3: Find Replace + Filter

Als weiteren Ansatz, könnten wir auch das Find Replace Tool verwenden. In diesem Fall können wir bei den Transaktionsdaten darauf verzichten, das Datum in einen Date-Datentyp umzuwandeln, stattdessen arbeiten wir hier mit dem Datum als "Text" (also String, V_String, WString oder V_WString) - falls wir das Datum nicht als Text bekommen, müssen wir es entsprechend konvertieren.

Für die Übersicht der Aktionen müssen wir allerdings [Von] und [Bis] wieder in ein Date verwandeln und danach mit Generate Rows eine Liste der Tage im Zeitraum erzeugen, das kennen wir von Variante 1 schon. Danach allerdings nutzen wir ein Formula Tool, um die erzeugten Einträge in Texte umzuwandeln; mit der Funktion DateTimeFormat erzeugen wir das Format, in dem die Transaktionsdaten vorliegen. 

      

Nun können wir das Find Replace Tool verwenden, um den Transaktionsdaten das Feld "Aktion" hinzuzufügen:

Wenn wir die Option "Append Field(s) to Record:" wählen, werden nur Felder hinzugefügt, der Name "Find Replace" ist dann vielleicht etwas verwirrend. Sofern es einen Treffer gibt, also das Datum in den Transaktionsdaten in der Tabelle mit den Aktionszeiträumen gefunden wird, ergänzt das Tool den Eintrag aus dem Feld [Aktion], sonst bleibt das Feld leer.

Die zugeordneten Datensätze müssen dann nur noch über ein Filter-Tool ausgewählt werden. Insgesamt sieht das dann so aus:

Eigentlich entspricht die Funktionalität des Find Replace Tools, wenn es wir hier beschrieben benutzt wird, dem SVERWEIS in Excel! Der Vorteil dieser Methode ist, dass im Gegensatz zu Join oder Append Fields jeder Datensatz nur genau eine Zuordnung bekommt. Wenn es also in der Liste der Aktionszeiträume Überschneidungen gibt (ein Tag gehört zu mehreren Aktionen), wird er auch doppelt zugeordnet - in der Regel ist das nicht gewollt. Dieses Problem vermeiden wir mit Find Replace. Allerdings sind wir etwas eingeschränkt, was den Feldtyp anbetrifft, über den eine Verknüpfung hergestellt wird.

Es gibt also wieder mal eine ganze Reihe von Möglichkeiten, ein Problem mit Alteryx zu lösen - welche am besten passt, hängt wie immer von der konkreten Aufgabenstellung ab.

Weitere Tipps Tuesday Beiträge

Dieser Eintrag ist Teil der Tipps Tuesday-Serie, alle Einträge dieser Serie findest du in unserem Index aufgelistet.