Använda postöverskridande funktioner Peek, Previous och Exists

Dessa funktioner används när ett värde från tidigare inlästa poster behövs för utvärdering av den aktuella posten.

I den här delen av introduktionskursen ska vi undersöka funktionerna Peek(), Previous() och Exists().

Peek()

Peek() returnerar värdet för ett fält i en tabell för en rad som redan har laddats. Radnumret kan anges, liksom tabellen. Om inget radnummer anges används posten som laddades senast.

Syntax:  

Raden måste vara ett heltal. 0 anger första posten, 1 andra posten osv. Negativa tal markerar ordningen från slutet av tabellen. -1 anger den senaste lästa posten.

Om ingen rad angivits, antas -1.

Tablename är en tabelletikett utan avslutande kolon. Om inget tablename har angetts antas den aktuella tabellen. Om det används utanför LOAD-satsen eller refererar till en annan tabell, måste tablename anges.

Previous()

Previous() returnerar värdet av uttrycket expr genom att använda data från en tidigare indatapost som inte uteslutits till följd av en where-sats. För den första posten i en intern tabell kommer funktionen att returnera NULL.

Syntax:  

Previous()-funktionen kan nästlas om man vill få tillgång till poster som ligger flera steg bakåt. Data hämtas direkt från indatakällan och man kan således referera även till fält som inte lästs in i Qlik Sense, dvs. även om de inte har lagrats i dess associativa databas.

Exists()

Exists() avgör om ett specifikt fältvärde redan har laddats in i fältet i dataladdningsskriptet. Funktionen returnerar TRUE eller FALSE, så att det kan användas i where-satsen för en LOAD-sats eller en IF-sats.

Syntax:  

Fältet måste finnas i de data som laddats hittills av skriptet. Expression är ett uttryck som utvärderar fältvärdet som ska hittas i den angivna fältet. Om uttrycket utelämnas, antas den aktuella postens värde i det angivna fältet.

Använda Peek() och Previous()

I sin enklaste form används Peek() och Previous() för att identifiera specifika värden i en tabell. Här är ett exempel på data i tabellen Employees som du kommer att ladda i den här övningen.

För närvarande hämtar den endast data för månad, anställningar och uppsägningar. Vi ska nu lägga till fält för Employee Count och Employee Var med hjälp av funktionerna Peek() och Previous(), för att se den månatliga skillnaden i det totala antalet medarbetare.

Gör följande:

1. Öppna appen Avancerad skriptvägledning.
2. Lägg till ett nytt skriptavsnitt i Skriptredigeraren.
3. Kalla delavsnittet Employees.

4. Klicka på Välj data under DataFiles i menyn till höger.

5. Ladda upp och välj sedan Employees.xlsx.
Anteckning om informationSe till att Embedded field names under Field names har valts för att inkludera namnen på tabellfälten när du laddar data.
6. Klicka på Infoga skript i fönstret Välj data från.

Ditt skript bör se ut så här:

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

7. Ändra skriptet så att det ser ut så här:

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

Datumen i fältet Date i arket Excel har formatet MM/DD/ÅÅÅÅ. Date-funktionen tillämpas på Date-fältet för att säkerställa att datumen tolkas rätt med hjälp av systemvariablernas format.

Peek()-funktionen gör att du kan identifiera alla värden som lästs in för ett definierat fält. Vi börjar med att titta om rowno() är lika med 1 i uttrycket. Om det är lika med 1, finns inte Employee Count, därför fyller vi i fältet med skillnaden mellan Hired minus Terminated.

Om rowno() är större än 1, så tittar vi på förra månadens Employee Count och lägger till det talet till skillnaden mellan den månadens Hired-medarbetare minus Terminated-medarbetare.

Observera även att vi använder (-1) i funktionen Peek(). Det gör att Qlik Sense använder posten ovanför den aktuella posten. Om (-1) inte anges kommer Qlik Sense att visa den föregående posten.

8. Lägg till följande i slutet av skriptet:

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

Previous()-funktionen gör att du kan identifiera det senaste värdet som lästs in för ett definierat fält. Vi börjar med att titta om rowno() är lika med 1 i uttrycket. Om det är lika med 1 vet vi att Employee Var inte finns eftersom det inte finns någon post för föregående månads Employee Count. Vi anger helt enkelt 0 för värdet.

Om rowno() är större än 1 vet vi att det kommer att finnas en Employee Var, så vi tittar på den föregående månadens Employee Count och subtraherar det talet från den aktuella månadens Employee Count för att få värdet i Employee Var-fältet.

Ditt skript bör se ut så här:

[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. Klicka på Ladda data.

Skapa en tabell i ett nytt ark i appöversikten med hjälp av Date, Hired, Terminated, Employee Count och Employee Var som kolumnerna i tabellen. Den resulterande tabellen bör se ut så här:

Peek() och Previous() gör att du kan rikta in dig på definierade rader i en tabell. Den största skillnaden mellan de två funktionerna är att med Peek() funktionen kan användaren titta på ett fält som inte har lästs in i skriptet tidigare, medan användaren med Previous()-funktionen endast kan titta på fält som redan har lästs in. Previous() använder LOAD-satsens indata medan Peek() använder LOAD-satsens utdata. (Samma som skillnaden mellan RecNo() och RowNo().) Det innebär att de två funktionerna kommer att bete sig olika om du har en Where-sats.

Funktionen Previous() skulle alltså vara bättre för att visa aktuellt värde jämfört med föregående värde. I exemplet beräknade vi variansen för medarbetare från månad till månad.

Funktionen Peek() skulle vara bättre för ett fält som inte har laddats in i tabellen tidigare eller för att fokusera på en specifik rad. Detta visades i exemplet när vi beräknade Employee Count genom att titta på föregående månads Employee Count och sedan lade till skillnaden mellan medarbetare som anställdes och medarbetare som slutade för den aktuella månaden. Kom ihåg att Employee Count inte var ett fält i den ursprungliga filen.

Använda Exists()

Exists()-funktionen används ofta med Where-satsen i skriptet för att ladda data, om relaterade data redan har laddats i datamodellen.

I följande exempel använder vi även funktionen Dual() för att tilldela numeriska värden till strängar.

Gör följande:

1. Skapa en ny app och ge den ett namn.
2. Lägg till ett nytt skriptavsnitt i Skriptredigeraren.
3. Kalla delavsnittet People.
4. Ange följande skript:

//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. Klicka på Ladda data.

I skriptet läses fälten Ageoch AgeBucket in endast om PersonID redan har lästs in i datamodellen.

Observera i AgeTemp-tabellen att det finns åldrar angivna för PersonID 11 och 12, men eftersom dessa ID:n inte har laddats in i datamodellen (i tabellen People) utesluts de på grund av Where Exists(PersonID)-satsen. Den här satsen kan även skrivas så här: Where Exists(PersonID, PersonID).

Skriptets utdata ser ut så här:



Om inga PersonID i AgeTemp-tabellen hade lästs in i datamodellen skulle inte fälten Ageoch AgeBucket ha kopplats till tabellen People. Du kan använda funktionen Exists() för att undvika överblivna poster/data i datamodellen, det vill säga Age- och AgeBucket-fält som inte har några personer associerade.

6. Skapa ett nytt ark och ge det ett namn.
7. Öppna det nya arket och klicka på Redigera ark.
8. Lägg till en standardtabell till arket med dimensionen AgeBucket och kalla visualiseringen Åldersgrupper.
9. Lägg till ett stapeldiagram till arket med dimensionen AgeBucket och måttet Count([AgeBucket]). Ge visualiseringen namnet Number of people in each age group.
10. Justera egenskaperna för tabellen och stapeldiagrammet som du vill ha dem och klicka på Klart.

    Arket bör se ut ungefär så här:

    

Dual()-funktionen är mycket användbar i skriptet eller i ett diagramuttryck när du behöver tilldela en sträng ett numeriskt värde.

I skriptet har du en applikation som läser in åldrar och du har bestämt dig för att lägga de åldrarna i buckets så att du kan skapa visualiseringar baserade på buckets för åldrar jämfört med verkliga åldrar. Det finns en bucket för personer under 25, en för personer mellan 25 och 35 och så vidare. Med hjälp av Dual()-funktionen kan buckets för ålder tilldelas numeriska värden som sedan kan användas för att sortera dessa buckets i en listbox eller ett diagram. Så precis som i apparket placeras ”No age” sist i listan.