Im dritten und letzten Eintrag unserer Blogreihe "Alteryx anwenden" möchten wir euch einen häufigen Anwendungsfall im Bereich der Datenverarbeitung näherbringen: Die Verknüpfung von mehreren Tabellen. In Excel würde man sowas wohl mit SVERWEIS (VLOOKUP) lösen - in Alteryx geht das aber einfacher. Übrigens: Blogeintrag #1 und #2 findest du hier: Teil 1, Teil 2.

Anwendungsfall: SVERWEIS in Alteryx - Tabellen einfach verknüpfen

Die Ausgangslage ist ziemlich einfach. Wir haben eine lange Tabelle namens «Sales.csv», die alle Verkäufe der vergangenen Monate enthält. Jede Zeile entspricht einer Transaktion und ist eindeutig über die Variable «SalesID» identifizierbar:

Eine sehr schöne Tabelle! Bei jedem Verkauf ist angegeben, welches Produkt in welcher Menge verkauft wurde. Leider jedoch fehlt der Rechnungsbetrag. Uns bleibt also nicht anderes übrig, als die Menge («Quantity») mit dem Preis einer Einheit zu multiplizieren. Der Haken: Produktpreise werden in einer eigenständigen Tabelle, «Product.csv», geführt. Als erstes müssen wir also die Produktpreise in die Ausgangstabelle «Sales.csv» integrieren. Eine genauere Betrachtung zeigt, dass beide Tabellen die Spalte «ProductID» enthalten:

In Excel könnte man die Preis-Spalte mittels SVERWEIS (VLOOKUP) rüberkopieren. Das Äquivalent zu SVERWEIS lautet in Alteryx auf den Namen "Find Replace". Das Tool wird per Drag & Drop auf die Arbeitsfläche gezogen und mit den beiden Tabellen verbunden. Anschliessend kann die zu migrierende Spalte angegeben werden:

Zweite Möglichkeit: Für die Migration von einer oder mehreren Spalten - oder sogar einer ganzen Tabelle - bietet Alteryx das Join-Tool. In der folgenden Abbildung werden zwei Tabellen miteinander verknüpft, ohne Umstände können aber auch mehrere Tabellen miteinander verknüpft werden. Einzige Angabe, die dazu nötig ist, ist was Primär- resp. Fremdschlüssel der beiden zu verknüpfenden Tabelle sind (in unserem Fall ist es die «ProductID»).

Gegenüber Excel ergeben sich also zwei klare Vorteile: Eine geringere Fehleranfälligkeit, da keine Formeln oder Markierungen von Spalten manuell vorgenommen werden müssen, und weiterhin eine unglaubliche Flexibilität in der Festlegung der Anzahl zu integrierenden Spalten oder Tabellen. Letzteres wird umso wichtiger, je mehr Tabellen verarbeitet werden sollen. Denn in Datenbanken bleibt es meistens nicht bei zwei Tabellen – mehrere Tabellen stehen durch verschiedene Fremdschlüssel in Beziehung zueinander.

Anwendungsfall 2.0: Relationales Datenmodell

In einem erweiterten Anwendungsfall könnte das beispielsweise so aussehen:

Wie ersichtlich besteht das Datenmodell aus vier Tabellen («Employee.csv», «Sales.csv», «Product.csv» und «ProductCategory.csv»). Die Primärschlüssel sind mit einem goldenem Schlüsselsymbol gekennzeichnet, die Fremdschlüssel mit einem silbernem Schlüsselsymbol. (Dieses Entity-Relationship-Modell haben wir übrigens in unserem Modellierungstool Innovator erstellt.)

Der Workflow, den wir erstellen werden, wird am Ende so aussehen:

Schritt 1

«Sales.csv» einlesen.

Schritt 2

Leider sind einige Datensätze unvollständig oder haben fehlerhafte Formate. Wir schliessen Datensätze ohne Datum aus und bringen alle Daten in das richtige Format:

Schritt 3

Im nächsten Schritt grenzen wir die Daten auf Januar 2018 ein. Dazu verwenden wir das praktische Filter-Tool von Alteryx.

Schritt 4

Bühne frei für den Star des Tags! Das Join-Tool verbindet zwei Tabellen in Windeseile. Via Drag und Drop gibt man an, welche beiden Tabellen gejoined werden sollen, danach muss nur noch angegeben werden, welche Kolonnen die Schlüssel darstellen. In unserem Fall ist dies zweimal die Kolonne «ProductID», siehe folgende Visualisierung:

Physisch sieht die Verknüpfung der Daten so aus:

Schritt 5

Mit dem Formula-Tool lassen sich eine beinahe unbegrenzte Anzahl Berechnungen durchführen. In vorliegendem Fall bleibt es bei simpler Arithmetik – wir berechnen Menge mal Preis, um für jeden Verkauf (jede «SalesID») den Umsatz zu berechnen.

Schritt 6

Das Summarize-Tool ist das Werkzeug der Wahl, wenn es um Aggregation von Tabellen geht. Zwei, drei Klicks und wir haben für jede «SalesPersonID» die Summe des Umsatzes berechnet:

Schritt 7

Das Resultat ist schonmal nicht schlecht, aber nicht wirklich spannend. Was noch fehlt, sind weitere Angaben zum Verkaufspersonal; diese sind in der Tabelle «Employee.csv» abgelegt. Diese Tabelle integrieren wir wiederum mit dem Join-Tool:

Wie in der Abbildung ersichtlich, erfolgt der Join über die beiden Felder «SalesPersonID» und «EmployeeID», die zwar unterschiedlich heissen, aber die gleichen Werte beinhalten, also als Fremd- respektive Primärschlüssel fungieren.

Schritt 8

Zu guter Letzt sortieren wir die Tabelle nach Umsatzgrösse, so dass die umsatzstärkste Verkäuferin oder Verkäufer zuoberst angezeigt wird. Wir erhalten folgendes finales Resultat:

Fertig! Und das Beste daran: Mit Alteryx lassen sich Workflows einfach automatisieren. Im vorliegenden Beispiel könnte man die Berechnung der Rangliste jede Woche durchführen - ohne zusätzlichen Aufwand und auch wenn sich die Datenquelle ändert.

Auf den Geschmack gekommen? Hier kannst du eine kostenlose Testversion von Alteryx herunterladen und das Tool testen. Bei Fragen steht dir Banian gerne zur Verfügung.