Utilizzo delle funzioni intra-record: Peek, Previous e Exists

Queste funzioni vengono utilizzate quando il valore di record di dati precedentemente caricati è necessario per valutare il record attuale.

In questa parte del tutorial si prenderanno in esame le funzioni Peek(), Previous() e Exists().

Peek()

Peek() restituisce il valore di un campo in una tabella per una riga che è già stata caricata. È possibile specificare il numero di riga così come la tabella. Se non viene specificato alcun numero di riga, verrà utilizzato l'ultimo record precedentemente caricato.

Sintassi:  

Row deve essere un numero intero. 0 indica il primo record, 1 il secondo e così via. I numeri negativi indicano l'ordine dalla fine della tabella. -1 indica l'ultimo record letto.

Se non viene dichiarata alcuna riga, viene utilizzato -1.

Tablename è un'etichetta di tabella senza i due punti finali. Se non è dichiarato tablename, viene utilizzata la tabella attuale. Se utilizzato al di fuori dell'istruzione LOAD o se fa riferimento a un'altra tabella, è necessario includere tablename.

Previous()

Previous() restituisce il valore dell'espressione expr utilizzando i dati del record di input precedente che non è stato eliminato a causa di una clausola where. Nel primo record di una tabella interna, la funzione restituirà NULL.

Sintassi:  

La funzione Previous() può essere nidificata per accedere a record ancora più precedenti. I dati vengono caricati direttamente dalla sorgente di input, rendendo possibile fare riferimento anche a campi che non sono stati caricati in Qlik Sense, vale a dire anche se non sono stati memorizzati nel database associato.

Exists()

Exists() determina se un valore di campo specifico è già stato caricato nel campo nello script di caricamento dei dati. La funzione restituisce TRUE o FALSE, quindi può essere utilizzata nella clausola where di un'istruzione LOAD o un'istruzione IF.

Sintassi:  

Il campo deve essere presente nei dati caricati finora dallo script. Expression è un'espressione che restituisce un valore da ricercare nel campo specificato. Se questa espressione viene omessa, viene utilizzato il valore del record attuale nel campo specificato.

Utilizzo di Peek() e Previous()

Nella loro forma più semplice, le funzioni Peek() e Previous() vengono utilizzate per identificare valori specifici all'interno di una tabella. Ecco un campione di dati della tabella Employees che verranno caricati in questo esercizio.

Al momento questa tabella raccoglie esclusivamente dati per mese, assunzioni e cessazioni, quindi si aggiungeranno campi per Employee Count e Employee Var, utilizzando le funzioni Peek() e Previous() per visualizzare la differenza mensile nel numero totale di dipendenti.

Procedere come indicato di seguito:

1. Aprire l'app Advanced Scripting Tutorial.
2. Aggiungere una nuova sezione dello script nell'editor caricamento dati.
3. Richiamare la sezione Employees.

4. Nel menu a destra, in DataFiles, fare clic su Seleziona dati.

5. Caricare e selezionare Employees.xlsx.
Nota informaticaIn Field names, assicurarsi che l'opzione Embedded field names sia selezionata per includere i nomi dei campi delle tabelle quando si caricano i dati.
6. Nella finestra Seleziona dati da, fare clic su Inserisci script.

Lo script avrà questo aspetto:

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

7. Modificare lo script nel modo seguente:

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

Le date nel campo Date del foglio Excel sono nel formato MM/GG/AAAA. Per assicurare la corretta interpretazione delle date che utilizzano il formato specificato dalle variabili di sistema, al campo Date viene applicata la funzione Date.

La funzione Peek() consente di identificare qualsiasi valore caricato per un campo definito. Nell'espressione si osserverà prima di tutto se rowno() è uguale a 1. Se è uguale a 1, Employee Count non sarà presente, quindi si compilerà il campo con la differenza tra Hired e Terminated.

Se rowno() è maggiore di 1, si farà riferimento al campo Employee Count dell'ultimo mese e si utilizzerà tale numero per aggiungere la differenza tra i dipendenti Hired e i dipendenti Terminated del mese.

Notare che nella funzione Peek() si utilizza (-1). In questo modo Qlik Sense cercherà il record sopra il record corrente. Se (-1) non è specificato, Qlik Sense presuppone che si desideri utilizzare il record precedente.

8. Aggiungere quanto segue alla fine dello 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];

La funzione Previous() consente di identificare l'ultimo valore caricato per un campo definito. Nell'espressione si osserverà prima di tutto se rowno() è uguale a 1. Se è uguale a 1, Employee Var non sarà presente poiché non vi sono record relativi al campo Employee Count del mese precedente. Pertanto si immette semplicemente il valore 0.

Se rowno() è maggiore di 1, sarà presente un campo Employee Var, quindi si osserverà il campo Employee Count dell'ultimo mese e si sottrarrà tale numero dal campo Employee Count del mese attuale per creare il valore nel campo Employee Var.

Lo script avrà questo aspetto:

[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. Fare clic su Carica dati.

In un nuovo foglio della panoramica App, creare una tabella utilizzando Date, Hired, Terminated, Employee Count e Employee Var come colonne. La tabella risultante avrà l'aspetto seguente:

Peek() e Previous() consentono di puntare a righe definite all'interno di una tabella. La più grande differenza tra le due funzioni è che la funzione Peek() consente all'utente di osservare un campo che non era stato caricato nello script in precedenza, mentre la funzione Previous() consente di osservare esclusivamente un campo caricato in precedenza. Previous() agisce sull'input dell'istruzione LOAD, mentre Peek() agisce sull'output dell'istruzione LOAD. Uguale alla differenza tra RecNo() e RowNo(). Ciò significa che il comportamento delle due funzioni sarà diverso in presenza di una clausola Where.

Quindi sarebbe meglio utilizzare la funzione Previous() quando è necessario mostrare il valore attuale invece del valore precedente. Nell'esempio la varianza dei dipendenti viene calcolata mese per mese.

È preferibile utilizzare la funzione Peek() quando si desidera puntare a un campo non caricato in precedenza nella tabella o quando è necessario puntare a una riga specifica. Ciò è mostrato nell'esempio in cui il campo Employee Count è stato calcolato mediante l'osservazione di Employee Count per il mese precedente ed è stata aggiunta la differenza tra le assunzioni e le cessazioni dei dipendenti per il mese attuale. Tenere presente che Employee Count non era un campo del file originale.

Utilizzo di Exists()

La funzione Exists() viene spesso utilizzata unitamente alla clausola Where nello script per caricare i dati se i dati correlati sono già stati caricati nel modello dati.

Nell'esempio seguente verrà utilizzata anche la funzione Dual() per assegnare valori numerici alle stringhe.

Procedere come indicato di seguito:

1. Creare una nuova app e assegnarle un nome.
2. Aggiungere una nuova sezione dello script nell'editor caricamento dati.
3. Richiamare la sezione People.
4. Immettere lo script seguente:

//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. Fare clic su Carica dati.

Nello script i campi Age e AgeBucket vengono caricati solo se il campo PersonID è già stato caricato nel modello dati.

Si prega di notare che nella tabella AgeTemp sono presenti le età inserite per PersonID 11 e 12, tuttavia, visto che tali ID non sono stati caricati nel modello dati (nella tabella People), vengono esclusi dalla clausola Where Exists(PersonID). Questa clausola può venire scritta anche nel modo seguente: Where Exists(PersonID, PersonID).

L'output dello script avrà il seguente aspetto:



Se nessuno dei campi PersonID nella tabella AgeTemp viene caricato nel modello dati, i campi Age e AgeBucket non verranno uniti alla tabella People. L'utilizzo della funzione Exists() può evitare la presenza di record/dati orfani nel modello dati, vale a dire campi Age e AgeBucket a cui non sono state associate delle persone.

6. Creare un nuovo foglio e assegnargli un nome.
7. Aprire il nuovo foglio e fare clic su Modifica foglio.
8. Aggiungere una tabella standard al foglio con la dimensione AgeBucket e assegnare alla visualizzazione il nome Age Groups.
9. Aggiungere un grafico a barre al foglio con la dimensione AgeBucket e la misura Count([AgeBucket]). Denominare la visualizzazione Number of people in each age group.
10. Regolare le proprietà della tabella e del grafico a barre in base alle preferenze, quindi fare clic su Fine.

    Il foglio dovrebbe ora avere l'aspetto seguente:

    

La funzione Dual() risulta utile nello script o in un'espressione del grafico quando occorre assegnare un valore numerico a una stringa.

All'interno dello script è presente un'applicazione che carica le età e si è deciso di suddividerle in fasce in modo da poter creare visualizzazioni basate sulle fasce d'età invece che sulle età effettive. È stata creata una fascia per le persone minori di 25 anni, tra i 25 anni e i 35 anni e così via. Grazie all'utilizzo della funzione Dual() alle fasce di età è possibile assegnare un valore numerico che può essere in seguito utilizzato per ordinare le fasce d'età in una casella di elenco o in un grafico. Quindi, come nel foglio dell'app, l'ordinamento inserisce "No age" alla fine dell'elenco.