Autor: Michał Santus
15 grudnia 2020

Czyszczenie danych w narzędziu Business Intelligence – Qlik Sense. Wtoiedziałek.

Jakość danych ma znaczenie

Przeciągnij, upuść, wizualizuj – tak działają nowoczesne narzędzia do wizualizacji danych. Dodajemy do tego: filtruj, wyciągaj wnioski, dziel się nimi – tak działa Qlik Sense.

Poprawne wnioski są gwarantowane przez poprawne dane. Jednak rzadko kiedy dane rzeczywiste są “czyste” i bez błędów. Jak sobie z tym poradzić i jakim arsenałem funkcji do czyszczenia danych (ang.: data cleansing) dysponuje Qlik – znajdziecie o tym kilka słów w poniższym wpisie.

Czyszczenie danych w Qlik Sense

“Sztuczna inteligencja” tego nie załatwi.

Jeżeli nie mamy zbudowanego modelu sztucznej inteligencji, który nauczył się czyścić dane na odpowiednio dużych zbiorach, czyszczenia ich nie możemy pozostawić w autonomii narzędzia, z którym pracujemy. Niewiele firm dysponuje wystarczającymi zbiorami danych, by móc dobrze nauczyć tego swoją sztuczną inteligencję. Proces taki jest też bardzo pracochłonny, a i tak wymaga wielu ciągłych, ręcznych ingerencji oraz regularnego usprawniania. Jednocześnie każda firma ma swoją specyfikę, przez co nie będą miały dobrego zastosowania wszelkie rozwiązania uniwersalne.

Pseudo-sztuczną inteligencją, którą widzimy w niektórych narzędziach, jest zastępowanie wartości według prostych schematów, np. powtórzenie wybrania n-pierwszych znaków lub powtórzenie wpisanej przez nas kalkulacji. To podejście bywa pomocne, choć czasami zwraca kuriozalne wyniki – jak na zdjęciu poniżej. Jednocześnie to, co widzimy w podglądzie na próbce danych może zadziałać zupełnie inaczej za tydzień lub miesiąc, gdy pojawią się nowe dane wsadowe.

Błędne automatyczne podpowiedzi Excel

Automatyczne podpowiedzi danych w niektórych narzędziach jest takie tylko z pozoru. Przyjrzyjmy się przykładowi z Excela. Wynik – niesatysfakcjonujący (Wtoiedziałek?).

Schodząc na ziemię…

Czyszczenie danych będziemy programować najczęściej świadomie. Posłużyć nam może do tego szereg funkcji i opcji dostępnych w Qlik Sense. W Qlikowym “Data Prep” czyli Menedżerze Danych możemy wykorzystać ich cały arsenał tworząc nowe pola pochodne.

Te same funkcje możemy wykorzystać pisząc ręcznie skrypt w Edytorze Skryptów. Część funkcji zastosujemy również z powodzeniem bezpośrednio w wizualizacji.

Data Manager Qlik

Spójrzmy na zdjęcie. Używając opcji Dodaj pole tworzymy nowe pole kalkulowane. Nadajemy mu nazwę i wstawiamy formułę. Możemy wybrać funkcję z listy i wstawić ją automatycznie lub wpisać formułę ręcznie, korzystając z formuł, z których przykładowe są opisane nieco niżej. Zauważmy, że od razu widzimy rezultat – pozwala to upewnić się, że formuła jest składniowo poprawna oraz, że zwraca oczekiwane rezultaty. W tym miejscu możemy nawet przeszukać i filtrować wyniki korzystając z lupy tuż pod “Podgląd”.

 

Techniki czyszczenia danych i użyte funkcje

Poniżej szereg przykładowych funkcji wykorzystywanych przy czyszczeniu i walidowaniu danych.

1. PurgeChar
Funkcja do czyszczenia tekstu wsadowego ze wskazanych znaków. Stosowana, by przykładowo:

  • oczyścić różnie wpisane numery NIP pozbywając się znaków specjalnych,
  • usunąć z numeru referencyjnego faktury wszelkie znaki specjalnej jakie księgowi czasami wstawiają by obejść systemowe ostrzeżenie przed podwójnym księgowaniem – by wykonać sprawdzenie duplikatów płatności w Qlik.
Formuła Wynik
PurgeChar(’951-245=24/54′,’-=/’) 9512452454

2. KeepChar
Działa podobnie do PurgeChar, oczyszcza tekst, ale w przypadku KeepChar podajemy zestaw znaków, które mogą w wyniku pozostać. Przydatne przy czyszczeniu:

  • numerów telefonów ze zbędnych nawiasów, myślników czy ukośników,
  • numerów kont bankowych, na przykład przy data cleansingu kartotek kontrahentów.
Formuła Wynik
KeepChar(’951-245=24/54′,’0123456789′) 9512452454

3. Capitalize
By porównać ciągi znaków, najlepiej najpierw je znormalizować, czyli sprowadzić do wspólnego formatu. Można to wykonać funkcją Upper, która przekształci wszystkie znaki na wielkie litery. Wielkość liter ma znaczenie w przypadku porównywania dwóch tekstów. Wielkość liter ma również znaczenie w czasie łączenia tabel z użyciem join oraz w kluczach w modelu asocjacyjnych. Nie ma natomiast znaczenia w wyszukiwaniu, czyli filtrowaniu pól.

Możemy użyć:

  • Capitalize, by słowo zaczynało się od wielkiej litery.
  • Upper, by podnieść wszystkie litery do capital.
  • Lower, by obniżyć wszystkie litery.
Formuła Wynik
Sprawdzenie, czy dwa teksty są tożsame:
Upper(‘Lublin’) = Upper(‘lublin’)		 True

4. MapSubstring
Funkcja pozwala podmienić wskazane znaki na ich zamienniki, przechodząc przez każdą literę w tekście. Możemy przykładowo zamienić wszystkie wersje litery o (ó, ö, o) na o.

Najpierw definiujemy mapę, potem podmieniamy znaki. Po tej operacji możemy bezpiecznie porównać dwa teksty.

Formuła Wynik
MapaLiter:
Z,Do
Ł,L
ó,o
ź,zMiasto’
Łódz
Łodź
ŁódźMapSubstring(‘MapaLiter’,Miasto)		 Lodz

5. ApplyMap
Polecenie skryptu do wydajnego podstawiania alternatywnych wartości. Jego działanie można porównać do funkcji WYSZUKAJ.PIONOWO (VLOOKUP) w Excelu. Typowo stosujemy to polecenie razem ze słownikiem, w którym z wielu wersji danego słowa zwracamy jedną, uspójnioną wersję.

Formuła Wynik
MapaMiast:
Z,Do
Warszawa,Warszawa
Warsiawa,Warszawa
Warsaw,Warszawa
Warschau,Warszawa
Wawa,Warszawa
W-wa,Warszawa
Varsovie,WarszawaApplyMap(‘MapaMiast’,Miasto’)		 Warszawa

Dobrym pomysłem może być powiązanie ApplyMap z Upper, tak by nie musieć utrzymywać mapy dla różnych kombinacji wielkości liter.

6. Geoanalityka i częściowe rozpoznawanie fałszywych nazw

Jeżeli wczytamy dane menedżerem danych i skorzystamy z profilowania, Qlik korzysta z wewnętrznego słownika nazwy największych miast na świecie i rozpozna ich różne wersje sprowadzając do jednej, właściwej. Opcja ta działa również w przypadku wyświetlania danych geograficznych z danych przekazywanych silnikowi Qlik na żywo. Silnik dokonuje wyszukania lokalizacji geograficznej lub kształtu danego obszaru na swoim serwerze porównując nazwę geograficzną punktu lub obszaru z bazą danych przechowującą różne wersje językowe.

Qlik Sense precyzowanie - lokalizacji, geolokalizacji

7. Dane geograficzne – precyzowanie zasięgu

Czasami zdarza się, że miejscowość o tej samej nazwie występuje w wielu województwach lub wręcz w wielu krajach. W takim przypadku dobrym pomysłem jest dostarczenie silnikowi Qlik informacji zawężającej wyszukiwanie lokalizacji do poziomu wyższego w hierarchii geografii, na przykład województwa czy kraju. Realizujemy to w ustawieniach danej warstwy w obiekcie mapy. W przykładzie poniżej Qlik lokalizuje Paryż we Francji – jest to najbardziej prawdopodobne, gdy mamy tylko nazwę miejscowości. Po doprecyzowaniu zakresu lokalizacji do kraju – odkrywamy Paryż w Polsce (między Bydgoszczą a Poznaniem).

Paryż we Francji

Przed doprecyzowaniem kraju.

Paryż w Polsce

Po doprecyzowaniu kraju – Paryż między Bydgoszczą a Poznaniem.

8. Subfield

Funkcja pozwalająca z jednego pola zawierającego wiele wartości utworzyć wiele pól zawierających po jednej wartości. Działa dwojako. Jeżeli podamy jej drugi parametr to funkcja wydobędzie tylko jedną wartość z pozycji określonej drugim parametrem (w przykładzie poniżej separatorem jest spacja a zwracana jest wartość pierwsza). Jeżeli nie podamy parametru funkcja zadziała, ale tylko w skrypcie ładowania danych – wydobędzie z danego pola wszystkie wartości mnożąc rekordy w czytanej tabeli tyle razy, ile różnych wartości napotka w danym polu.

Formuła Wynik
=Subfield(’Początek Koniec’,’ ’,1) Początek

9. Num# i Date#

Jeżeli dane liczbowe w źródle są przechowywane jako tekst, funkcja num z krzyżykiem zadziała jak odwrócone formatowanie. Przetworzy tekst na liczbę, według zadanego formatu. Szczególnie przydatne przy czytaniu danych eksportowanych z systemów ERP do CSV, gdzie minusy czasami dopisywane są na końcu liczby i nie będą rozpoznane domyślnie jako liczba ani przez Excela ani przez narzędzie BI. Analogicznie do funkcji num z krzyżykiem działa funkcja date z krzyżykiem.

Formuła Wynik
Date#(’2021-06-15 122100′, 'YYYY-MM-DD hhmmss’) Poprawna wartość numeryczna (44362,514583333), którą można dowolnie sformatować
Num#(’1500,15-’,’# ##.00;# ##.00-’) -1500,15

10. IsNum, IsText

Służy do sprawdzenia, czy dana wartość jest wartością numeryczną lub tekstową. Przydatne przy walidacji danych.

Formuła Wynik
IsNum(’Dwieście’) Fałsz
IsNum(‘200’) Prawda
IsText(’Dwieście’) Prawda
IsText(‘200’) Fałsz

11. Trim
Funkcja pozwalająca wyczyścić w tekście zbędne spacje, również na początku i na końcu tekstu.

Wynik  Formuła
Trim(‘ Tekst, który ma za dużo spacji’) Tekst, który ma za dużo spacji

12. Alt
Funkcja przyjmująca wiele danych wsadowych i zwracająca pierwszą z nich, która zawiera poprawną wartość liczbową.

Wynik Formuła
Alt(‘Sto’,’100’,’Setka’) 100

Monitor jakości danych

Po wdrożeniu wszystkich przetworzeń mających na celu oczyszczenie danych dobrą praktyką jest utworzenie raportu kontrolnego zwracającego wszelkie nowe błędy, jak na przykład nowe teksty wymagające dodania mappingu. W wersji chmurowej Qlik do tego raportu możemy podpiąć alert, który będzie nas od razu informował o wszelkich wykrytych anomaliach. Więcej o walidacji danych i raportach kontroli danych we wpisie https://datawizards.pl/blog/dashboardy-bez-kontekstu-czyli-o-analizach-ktore-rozumie-tylko-autor/