Inter-recordfuncties gebruiken: Peek, Previous en Exists

Deze functies worden gebruikt wanneer een waarde uit eerder geladen records van gegevens nodig is voor de evaluatie van de huidige record.

In dit gedeelte van de zelfstudie gaan we de functies Peek(), Previous() en Exists() nader onderzoeken.

Peek()

Peek() retourneert de waarde van een veld in een tabel voor een rij die al is geladen. Het rijnummer kan worden opgegeven, net als de tabel. Als er geen rijnummer is opgegeven, wordt het laatst geladen record gebruikt.

Syntaxis:  

Rij moet een geheel getal zijn. 0 geeft de eerste record aan, 1 de tweede, enzovoort. Negatieve nummers geven de volgorde vanaf het eind van de tabel aan. -1 geeft de laatste gelezen record aan.

Als geen rij wordt vermeld, wordt -1 verondersteld.

Tablename is een tabellabel zonder de dubbelepunt. Als geen tablename wordt vermeld, wordt de huidige tabel verondersteld. Als de tabelnaam buiten de LOAD-opdracht wordt gebruikt of als de tabelnaam verwijst naar een andere tabel, moet de tablename worden opgenomen.

Previous()

Previous() retourneert de waarde van de uitdrukking expr met gebruik van de gegevens uit de vorige invoerrecord die niet is genegeerd wegens een where-clausule. In de eerste record van een interne tabel retourneert de functie NULL.

Syntaxis:  

De functie Previous() kan worden genest om toegang te krijgen tot eerdere records. Gegevens worden rechtstreeks opgehaald uit de invoerbron. Zo kan ook worden verwezen naar velden die niet zijn geladen in Qlik Sense en dus niet zijn opgeslagen in de bijbehorende database.

Exists()

Exists() bepaalt of een specifieke veldwaarde al is geladen in het veld in het script voor het laden van gegevens. De functie retourneert TRUE of FALSE, zodat deze kan worden gebruikt in de where-clausule van een LOAD-opdracht of een IF-opdracht.

Syntaxis:  

Het veld moet bestaan in de gegevens die tot nu toe zijn geladen door het script. Expression is een uitdrukking die wordt geëvalueerd tot de veldwaarde die moet worden gezocht in het opgegeven veld. Als niets is opgegeven, wordt uitgegaan van de waarde van de huidige record in het opgegeven veld.

Peek() en Previous() gebruiken

In hun simpelste vorm worden Peek() en Previous() gebruikt voor het identificeren van specifieke waarden binnen een tabel. Hier vindt u een voorbeeld van de gegevens in de tabel Employees die u in deze oefening zult laden.

Op dit moment verzamelt deze uitsluitend gegevens voor maand, aangenomen werknemers en ontslagen werknemers, dus gaan we velden toevoegen voor Employee Count en Employee Var, met de functies Peek() en Previous(), om het maandelijkse verschil in het totale aantal werknemers te bekijken.

Doe het volgende:

1. Open de app Zelfstudie voor geavanceerd scriptgebruik.
2. Voeg een nieuwe scriptsectie toe in de Editor voor het laden van gegevens.
3. Roep de sectie Employees aan.

4. Onder DataFiles in het rechtermenu klikt u op Gegevens selecteren.

5. Upload en selecteer Employees.xlsx.
InformatieZorg ervoor dat Embedded field names onder Field names is geselecteerd om de namen van de tabelvelden mee te nemen bij het laden van de gegevens.
6. In het venster Selecteer gegevens uit klikt u op Script invoeren.

Uw script zou er als volgt moeten uitzien:

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

7. Wijzig het script zodat dit er nu als volgt uitziet:

   [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);

De datums in het veld Date in het Excel-werkblad hebben de indeling MM/DD/JJJJ. De functie Date wordt toegepast op het veld Date om ervoor te zorgen dat de datums correct worden geïnterpreteerd met behulp van de indeling van de systeemvariabelen.

Met de functie Peek() kunt u elke willekeurige waarde identificeren die is geladen voor een gedefinieerd veld. In de uitdrukking kijken we eerst of de rowno() gelijk is aan 1. Als dit gelijk is aan 1, bestaat er geen Employee Count, dus vullen we het veld met het verschil van Hired min Terminated.

Als de rowno() groter is dan 1, kijken we naar de Employee Count voor de afgelopen maand en tellen we dat getal op bij het verschil tussen Hired en Terminated werknemers voor die maand.

Zoals u kunt zien, maken we in de functie Peek() gebruik van een (-1). Dit geeft aan dat Qlik Sense naar de record boven de huidige record moet kijken. Als de (-1) niet is opgegeven, gaat Qlik Sense ervan uit dat u de voorgaande record wilt bekijken.

8. Voeg het volgende toe aan het eind van uw script:

[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];

Met de functie Previous() kunt u de laatste waarde identificeren die is geladen voor een gedefinieerd veld. In de uitdrukking kijken we eerst of de rowno() gelijk is aan 1. Als deze gelijk is aan 1, weten we dat er geen Employee Var is omdat er geen record voor Employee Count is voor de afgelopen maand. We voeren dus simpelweg 0 in als waarde.

Als rowno() groter is dan 1, weten we dat er een Employee Var is, dus kijken we naar Employee Count voor afgelopen maand en trekken dat getal af van Employee Count voor de huidige maand om de waarde te maken in het veld Employee Var.

Uw script zou er als volgt moeten uitzien:

[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. Klik op Gegevens laden.

In een nieuw werkblad in het app-overzicht maakt u een tabel met behulp van Date, Hired, Terminated, Employee Count en Employee Var als de kolommen van de tabel. De resulterende tabel zou er als volgt uit moeten zien:

Peek() en Previous() staan u toe gedefinieerde rijen binnen in een tabel te specificeren. Het grootste verschil tussen de twee functies is dat de functie Peek() de gebruiker in staat stelt een veld te bekijken dat niet eerder in het script was geladen, terwijl de functie Previous() alleen een eerder geladen veld kan bekijken. Previous() werkt op basis van de invoer van de LOAD-opdracht, terwijl Peek() werkt op basis van de uitvoer van de LOAD-opdracht. (Hetzelfde als het verschil tussen RecNo() en RowNo()) Dit betekent dat de twee functies zich anders gedragen als u een Where-clausule hebt.

Dus de functie Previous() is beter als u de huidige waarde in vergelijking met de vorige waarde moet weergeven. In het voorbeeld hebben we het verschil in aantallen werknemers van maand tot maand berekend.

De functie Peek() is beter als u zich richt op een veld dat nog niet eerder in de tabel is geladen of als u een specifieke rij moet weergeven. Dit hebben we laten zien in het voorbeeld waarbij we Employee Count berekenden door Employee Count van de voorafgaande maand te bekijken en het verschil tussen aangenomen en ontslagen werknemers voor de huidige maand daarbij op te tellen. Onthoud dat Employee Count geen veld in het oorspronkelijke bestand vormde

Exists() gebruiken

De functie Exists() wordt vaak gebruikt met de Where-clausule in het script om gegevens te laden als er al gerelateerde gegevens in het gegevensmodel zijn geladen.

In het volgende voorbeeld gaan we tevens de functie Dual() gebruiken om numerieke waarden toe te wijzen aan tekenreeksen.

Doe het volgende:

1. Maak een nieuwe app en geef deze een naam.
2. Voeg een nieuwe scriptsectie toe in de Editor voor het laden van gegevens.
3. Roep de sectie People aan.
4. Voer het volgende script in:

//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. Klik op Gegevens laden.

In het script worden de velden Age en AgeBucket alleen geladen als PersonID al is geladen in het gegevensmodel.

Zoals u ziet, worden in de tabel AgeTemp leeftijden vermeld voor PersonID 11 en 12, maar aangezien deze id's niet in het gegevensmodel zijn geladen (in de tabel People), worden zij uitgesloten door de Where Exists(PersonID)-clausule. Deze clausule kan ook als volgt worden geschreven: Where Exists(PersonID, PersonID).

De uitvoer van het script ziet er als volgt uit:



Als geen van de waarden voor PersonID in de tabel AgeTemp in het gegevensmodel is geladen, zijn de velden Age en AgeBucket niet samengevoegd tot de tabel People. Het gebruik van de functie Exists() kan helpen voorkomen dat er weesrecords/-gegevens ontstaan in het gegevensmodel. Met andere woorden dat de velden Age en AgeBucket geen bijbehorende personen hebben.

6. Maak een nieuw werkblad en geef dit een naam.
7. Open het nieuwe werkblad en klik op Het werkblad bewerken.
8. Voeg een standaardtabel toe aan het werkblad met de dimensie AgeBucket en geef de visualisatie de naam Leeftijdsgroepen.
9. Voeg een staafdiagram toe aan het werkblad met de dimensie AgeBucket en de meting Count([AgeBucket]). Geef de visualisatie Number of people in each age group een naam.
10. Pas de eigenschappen van de tabel en het staafdiagram aan uw voorkeuren aan en klik daarna op Gereed.

    U zou nu een werkblad moeten hebben dat hierop lijkt:

    

De functie Dual() is nuttig in het script of in een diagramuitdrukking, als een numerieke waarde moet worden toegewezen aan een tekenreeks.

In het script hierboven hebt u een toepassing die leeftijden laadt en u hebt besloten om deze leeftijden op te nemen in buckets zodat u visualisaties kunt maken op basis van de leeftijdsbuckets en deze kunt vergelijken met de daadwerkelijke leeftijden. Er is een bucket voor mensen jonger dan 25, mensen tussen 25 en 35 enzovoort. Via de functie Dual() kunnen aan de leeftijdsbuckets een numerieke waarde worden toegewezen die kan worden gebruikt voor het sorteren van de leeftijdsbuckets in een lijstvak of in een diagram. Dus, net als in het werkblad van de app, wordt "Geen leeftijd" aan het einde van de lijst geplaatst door de sorteerfunctie.