Dublicate Checker - Datenbereinigung mit QlikView

Unsere Kunden setzen QlikView gerne ein um die Datenqualität Ihrer Systeme zu sichten. Das Assoziative Grün-Weiss-Grau Datenmodell, und QlikView's Eigenschaft jeden distinkten Wert eines Feldes in einer Listbox anzuzeigen, hilft oft einen schnellen Überblick über den Datenbestand zu bekommen.

Typische Datenqualitätsprobleme in QlikView sichtbar gemacht:  Unterschiedliche Schreibweisen 

Auch bei Migrationen, Umstieg auf neue Systeme und Zusammenführung von Alt-Daten kommt QlikView immer wieder zum Einsatz. Zuletzt bekam ich eine interessante Anfrage von einem Kunden: Ist es mit QlikView einfach möglich Duplikate zu finden? Pflegt man etwa den Artikelstamm in zwei (oder vielleicht sogar mehr) Systemen, kann ich überprüfen ob die Artikel-Ausprägungen für eine ArtikelNr in beiden Systemen gleich ist?

In meinem kleinen Beispiel hat jede Artikel  (ID) drei Ausprägungen: Name, Preis, ShortName.
Aus diesen beiden Quellsystemen sollen Duplikate gefiltert werden


Ziel ist es nun zu überprüfen ob die Kombination aus "Name, Preis und ShortName" sich in den beiden Systemen unterscheiden. QlikView bietet dafür eine praktische Funktion: autonumberhash256. An dieser Funktion kann man eine beliebige Anzahl an Feldern übergeben. Für jede Wert-Kombination wird innerhalb eines QlikView Scripts ein eindeutiger Wert berechnet.

In unserem Beispiel rufen wir also die Funktion mit 
autonumberhash256(Name,Preis,ShortName) 
auf um ein neues Feld "hash" zu generieren.


Wie man sehen kann wird für die Ausprägung (1,Apple,10,A) in beiden Datenquellen der Hashwert 1 generiert. Dieser Datensatz ist also in beiden Quellsystemen gleich, somit keine Dublette. Im Gegensatz dazu liefert die Funktion für die Werte (2,Orange, 11, ORANG) und (2, Orange,11, O) zwei unterschiedliche Hashwerte. Damit hat die Artikel mit ID=2 eine Dublette.

Im nächsten Schritt des QlikView Scripts markiert man sich nun mittels eines group by und einem load above load alle IDs die mehr als einen unterschiedlichen Hashwert aufweisen --> denn genau das sind meine Dubletten. In unserem Fall sind es die zwei Produkte Orange und Banana.

Die zwei gefundenen Dublikate. Artikel 2 und Artikel 3

Die .qvw mit dem Dublicate Checker finden Sie unter diesem Link. Einfach die Felder der autonumberhash256()-Funktion an Ihre Datenquellen anpassen. Der Ansatz funktioniert übrigens auch mit mehr als 2 Source Systemen - autonumberhash256() sei Dank.

Weiters schlummert auf meiner Festplatte ein altes Beispiel (von einem unbekannten Author) mit einem phonetischen Dublettenchecker . Gelöst mit einem Macro das im Skript aufgerufen wird. Sicherlich sehr interessant wenn man mehrere Adressbestände abgleichen möchte. Vielleicht wird das ja mal ein eigener Blogeintrag ;-)





5 Response to "Dublicate Checker - Datenbereinigung mit QlikView"

  1. Fabian says:

    Schlauer Ansatz, danke für's teilen!
    Freue mich auf die Sache mit der phonetischen Suche :-)

    Anonym says:

    Interessanter Eintrag - ich hatte mal einen ähnlichen Auftrag - da ging es nur um eine Tabelle

    Ausgangslage:
    Wir haben im Spitalverbund an 4 Standorten SAP als Patientensystem mit dezentraler Erfassung und Pflege der Daten.
    Bei der Einführung von SAP 2007 wurden die bestehenden Daten aus dem Vorsystem (mit 4 getrennten Mandanten) übernommen.
    Es wurde eine Auswertung gewünscht, die regelmässig doppelt angelegte Patienten listet.
    Ziel war, physisch identische Personen mit mehreren Stammdatensätzen zu finden, die:
    - umgezogen waren
    - geheiratet hatten
    - Tippfehler jedweder Art in den Daten haben
    - vertauschte Felder haben (Kunta? Ist das der Vor- oder Nachname -- Ja!)
    - Zahlendreher im Geburtsdatum

    Mein Ansatz: Zur Laufzeit aus diversen Feldern dynamisch Keys bauen und dann mit den Daten zusammen bringen.
    Identische Keys mit abweichender Patientennummer (=PID) sind potentiell doppelt angelegte Patienten.

    Resultat: Bei 320.000 Datensätzen blieben initial 1.200 fehlerhafte Datensätze hängen, Fehlerquote ca 0.5%
    Mittlerweile wurde meine Report um ein Projekt zur Verbesserung der Datenqualität und einer 60%-Stelle erweitert ^^

    Ob der Ansatz cool oder umständlich ist - keine Ahnung - aber er funktioniert prima.

    Claus Gittner

    REM Alle nicht stornierten Datensätze der Patienten vorladen
    Daten_Vor:
    LOAD DISTINCT
    *
    FROM
    $(vPfadDataSAP)NPAT.qvd(qvd)
    WHERE
    STORN <> 'X';

    REM Verschiedene Kombinationen der Felder bilden jeweils den Schlüssel; Trenner ist @
    Schlüssel:
    LOAD * INLINE [
    Key
    Nachname & '_' & Vorname & '_' & Datum & '_' & Telefon@Nachname & '_' & Vorname & '_' & Datum & '_' & Telefon
    Nachname & '_' & Vorname & '_' & Datum & '_' & PLZ & '_' & Ort@Nachname & '_' & Vorname & '_' & Datum & '_' & PLZ & '_' & Ort
    ];

    LET vAnz = 2;

    FOR i = 0 TO vAnz - 1

    QUALIFY *;
    UNQUALIFY key_Daten;

    REM Schlüssel zur Laufzeit bilden
    LET key_Daten1 = SUBFIELD(PEEK('Key', $(i), 'Schlüssel'), '@', 1);
    LET key_Daten2 = SUBFIELD(PEEK('Key', $(i), 'Schlüssel'), '@', 2);

    REM Datensätze zum 1. Mal mit Schlüssel zusammen laden
    1:
    LOAD
    *,
    $(key_Daten1) AS key_Daten
    RESIDENT
    Daten_Vor;

    REM Datensätze zum 2. Mal mit Schlüssel zusammen laden
    2:
    JOIN LOAD
    *,
    $(key_Daten2) AS key_Daten
    RESIDENT
    Daten_Vor;

    UNQUALIFY *;

    REM Da auch identische Datensätze gefunden werden, erstmal die kleinere PID zum 1. DS machen
    Doppelte:
    LOAD
    IF(PID_1 > PID_2, PID_2 & '_' & PID_1, PID_1 & '_' & PID_2) AS Doppelter
    FROM
    $(vPfadDataStamm)tbl_Doppelte_PID.qvd(qvd);

    REM Alle Datensätze mit unterschiedlichen PIDs laden
    Patient_Liste:
    LOAD DISTINCT
    *
    RESIDENT
    1
    WHERE
    NUM([1.PID]) < NUM([2.PID]);

    DROP TABLES
    Doppelte,
    1;
    NEXT i

    DROP TABLE
    Schlüssel,
    Daten_Vor;

    Ich bin momentan auf der Suche nach einer Lösung, einem Dublettenchecker. Es geht um Adressbestände aus unterschiedlichen Systemen, die in ein System übernommen werden sollen.
    Es wäre nur zu schön, wenn dieses eine alte Beispiel (von einem unbekannten Author) mit einem phonetischen Dublettenchecker, nicht mehr auf der Festplatte schlummern würde :-)

    Hallo!
    Schicken Sie mir Ihren Kontakt unter http://www.heldendaten.net/#!contact/c1d94, dann kann ich Ihnen die QlikView Applikation zukommen lassen.

    Tobias says:

    Hallo, ich bin ebenfalls auf der Suche nach einem Duplikatschecker zum Abgleich unterschiedlicher Adressdatenbanken. Könnte ich den phonetischen Dublettenchecker ebenfalls bekommen?

Kommentar veröffentlichen

heldendaten GmbH,2017