Używanie funkcji międzywierszowych Peek, Previous i Exists

Tych funkcji używa się, gdy obliczenie wartości bieżącego rekordu wymaga wartości z wcześniej załadowanych rekordów danych.

W tej części kursu zajmiemy się funkcjami Peek(), Previous() i Exists().

Peek()

Peek() wyszukuje wartość pola w tabeli z wiersza, który został już załadowany. Numer wiersza może być określony, podobnie jak tabela. Jeśli nie określono numeru wiersza, zostanie użyty ostatnio załadowany rekord.

Składnia:  

Wiersz musi być liczbą całkowitą. 0 oznacza pierwszy rekord, 1 drugi rekord itd. Liczby ujemne określają kolejność od końca tabeli. -1 oznacza ostatni wczytany rekord.

Jeśli nie zostanie podany wiersz, przyjmowana jest wartość -1.

Tablename jest etykietą tabeli bez końcowego dwukropka. Jeśli argument tablename nie zostanie podany, przyjmowana jest bieżąca tabela. W przypadku używania poza instrukcją LOAD lub podczas odwołania do innej tabeli należy uwzględnić tablename.

Previous()

Funkcja Previous() wyszukuje wartość wyrażenia expr przy użyciu danych z poprzedniego rekordu wejściowego, który nie został odrzucony z powodu klauzuli where. W przypadku pierwszego wiersza tabeli wewnętrznej funkcja zwróci wartość NULL.

Składnia:  

Funkcja Previous() może być zagnieżdżona w celu uzyskiwania dostępu do bardziej odległych wierszy. Dane są pobierane bezpośrednio ze źródła danych wejściowych, co umożliwia odwoływanie się również do pól, które nie zostały załadowane do programu Qlik Sense, czyli nawet gdy nie zostały zapisane w powiązanej bazie danych.

Exists()

Funkcja Exists() określa, czy podana wartość pola została już załadowana w polu w skrypcie ładowania danych. Funkcja zwraca wartość TRUE lub FALSE, dzięki czemu może zostać użyta w klauzuli where instrukcji LOAD lub instrukcji IF.

Składnia:  

Pole musi istnieć w danych załadowanych do tej pory przez skrypt. Expression jest wyrażeniem odwołującym się do poszukiwanej wartości pola w określonym polu. Pominięcie tego argumentu odpowiada przyjęciu we wskazanym polu wartości bieżącego rekord.

Używanie funkcji Peek() i Previous()

W najprostszej postaci funkcje Peek() i Previous() służą do identyfikowania konkretnych wartości w tabeli. Poniżej znajduje się próbka danych w tabeli Employees, która zostanie załadowana w tym ćwiczeniu.

Obecnie gromadzone są tylko dane dotyczące miesięcy, osób zatrudnianych i kończących zatrudnienie, dlatego dodamy pola dla wartości Employee Count i Employee Var, używając funkcji Peek() i Previous(), aby sprawdzić różnicę łącznej liczby pracowników w ujęciu miesięcznym.

Wykonaj następujące czynności:

1. Otwórz aplikację Advanced Scripting Tutorial.
2. Dodaj nową sekcję skryptu w edytorze ładowania danych.
3. Wywołaj sekcję Employees.

4. W sekcji DataFiles dostępnej po prawej stronie kliknij przycisk Wybierz dane.

5. Prześlij, a następnie wybierz Employees.xlsx.
InformacjaUpewnij się, że w obszarze Field names wybrana jest opcja Embedded field names, co pozwala na uwzględnienie nazw pól tabel podczas ładowania danych.
6. W oknie Wybierz dane z kliknij przycisk Wstaw skrypt.

Skrypt powinien wyglądać następująco:

LOAD "Date", Hired, Terminated FROM [lib://DataFiles/Employees.xlsx] (ooxml, embedded labels, table is Sheet1);

7. Zmodyfikuj skrypt w taki sposób, aby wyglądał następująco:

   [Employees Init]:
   LOAD
       rowno() as Row,
       Date(Date) as Date,
       Hired,
       Terminated,
       If(rowno()=1, Hired-Terminated,  peek([Employee Count], -1)+(Hired-Terminated)) as [Employee Count]
   FROM [lib://DataFiles/Employees.xlsx]		
   (ooxml, embedded labels, table is Sheet1);

Daty w polu Date w arkuszu Excel są zapisane w formacie MM/DD/RRRR. Aby upewnić się, że daty są poprawnie interpretowane z użyciem formatu ze zmiennych systemowych, względem pola Date stosowana jest funkcja Date.

Funkcja Peek() umożliwia zidentyfikowanie dowolnej wartości załadowanej dla zdefiniowanego pola. W tym wyrażeniu najpierw sprawdzamy, czy wartość rowno() jest równa 1. Jeżeli tak, to Employee Count nie będzie istnieć, więc wypełnimy pole z różnicą między Hired a Terminated.

Jeśli funkcja rowno() jest większa od 1, sprawdzamy wartość Employee Count z ostatniego miesiąca i dodajemy tę liczbę do różnicy liczby pracowników Hired minus Terminated z tego miesiąca.

Zauważ też, że w funkcji Peek() używamy (-1). Dla aplikacji Qlik Sense oznacza to, że ma sprawdzić rekord nad bieżącym rekordem. Jeśli wartość (-1) nie zostanie podana, aplikacja Qlik Sense przyjmie, że wymagane jest sprawdzenie poprzedniego rekordu.

8. Dodaj następujący kod na końcu skryptu:

[Employee Count]:
LOAD
	Row,
	Date,
	Hired,
	Terminated,
	[Employee Count],
	If(rowno()=1,0,[Employee Count]-Previous([Employee Count])) as [Employee Var]
Resident [Employees Init] Order By Row asc;
Drop Table [Employees Init];

Funkcja Previous() umożliwia zidentyfikowanie ostatniej wartości załadowanej dla zdefiniowanego pola. W wyrażeniu tym najpierw sprawdzamy, czy wartość rowno() jest równa 1. Jeżeli tak, to wiemy, że nie będzie istnieć Employee Var, ponieważ nie ma żadnego zapisu dla poprzedniego miesiąca w przypadku sekcji Employee Count. Więc dla tej wartości wpisujemy 0.

Jeśli funkcja rowno() jest większa niż 1, wiemy, że będzie istniała wartość Employee Var, więc sprawdzamy wartość Employee Count z ostatniego miesiąca i odejmujemy tę liczbę od wartości Employee Count z bieżącego miesiąca, aby utworzyć wartość w polu Employee Var.

Skrypt powinien wyglądać następująco:

[Employees Init]:
LOAD
    rowno() as Row,
    Date(Date) as Date,
    Hired,
    Terminated,
    If(rowno()=1, Hired-Terminated,  peek([Employee Count], -1)+(Hired-Terminated)) as [Employee Count]
FROM [lib://DataFiles/Employees.xlsx]
(ooxml, embedded labels, table is Sheet1);

[Employee Count]:
LOAD
    Row,
    Date,
    Hired,
    Terminated,
    [Employee Count],
    If(rowno()=1,0,[Employee Count]-Previous([Employee Count])) as [Employee Var]
Resident [Employees Init] Order By Row asc;	
Drop Table [Employees Init];

9. Kliknij polecenie Ładuj dane.

W nowym arkuszu w przeglądzie aplikacji utwórz tabelę z kolumnami Date, Hired, Terminated, Employee Count i Employee Var. Wynikowa tabela powinna wyglądać następująco:

Funkcje Peek() i Previous() pozwalają wskazać zdefiniowane wiersze w tabeli. Największa różnica między tymi dwiema funkcjami polega na tym, że funkcja Peek() umożliwia użytkownikowi sprawdzanie pola, które nie było poprzednio ładowane do skryptu, natomiast funkcja Previous() może tylko sprawdzać pola, które zostały poprzednio załadowane. Funkcja Previous() działa na danych wejściowych dla instrukcji LOAD, natomiast funkcja Peek() działa na danych wyjściowych instrukcji LOAD. (Podobna różnica występuje między funkcjami RecNo() i RowNo()). Oznacza to, że obie te funkcje będą zachowywać się inaczej, jeśli istnieje klauzula Where.

Więc funkcja Previous() jest bardziej odpowiednia, kiedy wymagane jest wyświetlenie aktualnej wartości w porównaniu z poprzednią wartością. W przykładzie obliczyliśmy wariancję liczby pracowników z miesiąca na miesiąc.

Funkcja Peek() jest lepsza, gdy pole docelowe nie zostało wcześniej załadowane do tabeli lub gdy wybierany jest konkretny wiersz. Zostało to przedstawione w przykładzie, w którym obliczyliśmy wartość Employee Count, sprawdzając wartość Employee Count z poprzedniego miesiąca i dodając różnicę między liczbą zatrudnionych a zwolnionych pracowników dla miesiąca bieżącego. Należy pamiętać, że wartość Employee Count nie była polem w pierwotnym pliku.

Używanie funkcji Exists()

Funkcja Exists() jest często używana z klauzulą Where w skrypcie w celu ładowania danych w sytuacji, gdy dane powiązane zostały już załadowane w modelu danych.

W poniższym przykładzie używamy również funkcji Dual() w celu przypisania wartości liczbowych do łańcuchów.

Wykonaj następujące czynności:

1. Utwórz nową aplikację i nadaj jej nazwę.
2. Dodaj nową sekcję skryptu w edytorze ładowania danych.
3. Wywołaj sekcję People.
4. Wprowadź następujący skrypt:

//Add dummy people data
PeopleTemp:
LOAD * INLINE [
PersonID, Person
1, Jane
2, Joe
3, Shawn
4, Sue
5, Frank
6, Mike
7, Gloria
8, Mary
9, Steven,
10, Bill
];
 
//Add dummy age data
AgeTemp:
LOAD * INLINE [
PersonID, Age
1, 23
2, 45
3, 43
4, 30
5, 40
6, 32
7, 45
8, 54
9,
10, 61
11, 21
12, 39
];
 
//LOAD new table with people
People:
NoConcatenate LOAD
    PersonID,
    Person
Resident PeopleTemp;
 
Drop Table PeopleTemp;
 
//Add age and age bucket fields to the People table
Left Join (People)
LOAD
    PersonID,
    Age,
	If(IsNull(Age) or Age='', Dual('No age', 5),
	 If(Age<25, Dual('Under 25', 1),
	  If(Age>=25 and Age <35, Dual('25-34', 2),
	   If(Age>=35 and Age<50, Dual('35-49' , 3),
	    If(Age>=50, Dual('50 or over', 4)
	     ))))) as AgeBucket
Resident AgeTemp
Where Exists(PersonID);
 
DROP Table AgeTemp;

5. Kliknij polecenie Ładuj dane.

W skrypcie pola Age i AgeBucket są ładowane tylko wtedy, gdy identyfikator PersonID został już załadowany w modelu danych.

Zwróć uwagę na to, że tabela AgeTemp zawiera wartości wieku dla identyfikatorów PersonID 11 i 12, ale ponieważ te identyfikatory nie zostały załadowane w modelu danych (w tabeli People), dlatego zostały wykluczone przez klauzulę Where Exists(PersonID). Tę klauzulę można również zapisać następująco: Where Exists(PersonID, PersonID).

Dane wyjściowe skryptu wyglądają następująco:



Gdyby żaden z identyfikatorów PersonID w tabeli AgeTemp nie został załadowany do modelu danych, wówczas pola Age i AgeBucket nie zostałyby sprzężone do tabeli People. Funkcja Exists() może zapobiegać powstawaniu osieroconych wierszy/danych w modelu danych, czyli pól Age i AgeBucket, które nie zawierają żadnych danych powiązanych osób.

6. Utwórz nowy arkusz i nadaj mu nazwę.
7. Otwórz nowy arkusz i kliknij przycisk Edytuj arkusz.
8. Do arkusza dodaj standardową tabelę z wymiarem AgeBucket i nadaj wizualizacji nazwę Grupy wiekowe.
9. Dodaj do arkusza wykres słupkowy z wymiarem AgeBucketoraz miarą Count([AgeBucket]). Nadaj nazwę wizualizacji Number of people in each age group.
10. Dostosuj właściwości tabeli i wykresu słupkowego zgodnie z preferencjami i kliknij polecenie Gotowe.

    Arkusz powinien wyglądać podobnie do niniejszego:

    

Funkcja Dual() jest bardzo użyteczna w skrypcie albo w wyrażeniu wykresu, gdy nie ma potrzeby przypisywania wartości liczbowej do ciągu znaków.

W powyższym skrypcie dostępna jest aplikacja, która ładuje wartości wieku. Te wartości zostały umieszczone w przedziałach, co umożliwia utworzenie wizualizacji na podstawie przedziałów, zamiast na podstawie rzeczywistych wartości wieku. Istnieje przedział dla osób w wieku poniżej 25 lat, w zakresie od 25 do 35 lat itd. Za pomocą funkcji Dual() można przypisać do przedziałów wieku wartość liczbową, która później może być używana w celu sortowania przedziałów na liście wartości lub w wykresie. Sortowanie, podobnie jak w arkuszu aplikacji, umieszcza przedział „No age” na końcu listy.