Uso de funciones inter-registro: Peek, Previous y Exists

Estas funciones se utilizan cuando se necesita un valor de registros de datos previamente cargados para la evaluación del registro actual.

En esta parte del tutorial, examinaremos las funciones Peek(), Previous() y Exists().

Peek()

Peek() devuelve el valor de un campo en una tabla para una fila que ya se ha cargado o que existe en la memoria interna. El número de fila se puede especificar, así como la tabla. Si no se especifica un número de fila, se utilizará el último registro cargado anteriormente.

Sintaxis:  

Fila debe ser un entero. 0 denota el primer registro, 1 el segundo y así sucesivamente. Los números negativos indican el orden desde el final de la tabla. -1 denota el último registro leído.

Si no se establece fila alguna, se presupone -1.

Tablename es una etiqueta de tabla que no finaliza en dos puntos. Si no se especifica tablename, se presupone la tabla actual. Si se utiliza fuera de la sentencia LOAD o se refiere a otra tabla, debe incluirse tablename.

Previous()

Previous() halla el valor de la expresión expr utilizando datos del registro de entrada anterior que no se han descartado debido a una cláusula where. En el primer registro de una tabla interna, la función devolverá NULL.

Sintaxis:  

La función Previous() puede anidarse para acceder a registros anteriores. Los datos se recuperan directamente de la fuente de entrada; esto también hace posible consultar los campos que no se hayan cargado en Qlik Sense, es decir, aunque no se hayan almacenado en la base de datos asociativa.

Exists()

Exists() determina si un valor de campo específico ya se ha cargado en el campo en el script de carga de datos. La función devuelve TRUE o FALSE, así que se puede utilizar en la cláusula where de una sentencia LOAD o IF.

Sintaxis:  

El campo debe existir en los datos cargados hasta ahora por el script. Expression es una expresión que evalúa el valor del campo que buscar en el campo especificado. Si se omite, se asume el valor del registro actual en el campo especificado.

Uso de Peek() y Previous()

En su forma más simple, Peek() y Previous() sirven para identificar valores específicos en una tabla. Aquí tiene una muestra de los datos de la tabla Employees que cargará en este ejercicio.

Actualmente esto solo recopila datos de meses, contrataciones y despidos, por lo que vamos a agregar campos para Employee Count y Employee Var, usando las funciones Peek() y Previous(), para ver la diferencia por mes en el total de empleados.

Haga lo siguiente:

1. Abra la app Tutorial de script avanzado.
2. Agregue una nueva sección de script en el Editor de carga de datos.
3. Denomine a la sección Employees.

4. En DataFiles en el menú a la derecha, haga clic en Seleccionar datos.

5. Cargue y después seleccione Employees.xlsx.
Nota informativaEn Field names, asegúrese de que Embedded field names esté seleccionado para que se incluyan los nombres de los campos de tabla cuando cargue los datos.
6. En la ventana Seleccionar datos de, haga clic en Insertar script.

Su script debería tener el aspecto siguiente:

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

7. Modifique el script para que su aspecto sea como este:

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

Las fechas en el campo Date de la hoja de Excel están en el formato MM/DD/YYYY. Para asegurarse de que las fechas se interpreten correctamente utilizando el formato de las variables del sistema, la función Date se aplica al campo Date.

La función Peek() le permite identificar cualquier valor cargado para un campo definido. En la expresión, primero veremos si rowno() es igual a 1. Si es igual a 1, no existirá ninguna Employee Count, por lo que simplemente rellenaremos el campo con la diferencia de Hired menos Terminated.

Si rowno() es mayor que 1, observamos el número de empleados (Employee Count) del mes pasado y usamos ese número para sumarlo a la diferencia de los empleados contratados (Hired) menos los empleados cesados (Terminated) de ese mes.

Tenga en cuenta también que en la función Peek() usamos un valor (-1). Esto indica a Qlik Sense que busque en el registro anterior al registro actual. Si el (-1) no está especificado, Qlik Sense supondrá que desea buscar en el registro anterior.

8. Agregue lo siguiente al final de su 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 función Previous() le permite identificar el último valor cargado para un campo definido. En la expresión primero observamos si rowno() es igual a 1. Si es igual a 1, sabemos que no habrá ningún Employee Var porque no hay registro para el Employee Count del mes anterior. Así que simplemente introducimos 0 para el valor.

Si rowno() es mayor que 1, sabemos que habrá un Employee Var, por lo que analizaremos el Employee Count del mes pasado y restaremos ese número del Employee Count del mes actual para crear el valor en el campo Employee Var.

Su script debería tener el aspecto siguiente:

[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. Haga clic en Cargar datos.

En una nueva hoja en la vista general de app cree una tabla usando Date, Hired, Terminated, Employee Count y Employee Var como columnas de la tabla. La tabla resultante deberá tener el siguiente aspecto:

Peek() y Previous() le permiten apuntar a filas definidas de una tabla. La diferencia más notable entre las dos funciones es que la función Peek() permite al usuario buscar en un campo que no estaba cargado anteriormente en el script, mientras que la función Previous() solo puede buscar en un campo previamente cargado. Previous() opera en la entrada de la sentencia LOAD, mientras que Peek() opera en el resultado de la sentencia LOAD. (Igual que la diferencia entre RecNo() y RowNo().) Esto significa que las dos funciones se comportarán de manera diferente si tenemos una cláusula Where.

Por tanto, la función Previous() sería más adecuada para cuando necesite mostrar el valor actual en vez del anterior. En el ejemplo, hemos calculado la varianza entre meses.

La función Peek() sería más adecuada cuando utilice un campo que no se ha cargado previamente en la tabla o cuando necesite definir como objetivo una fila específica. Esto se mostró en el ejemplo en el que calculamos Employee Count analizando el Employee Count del mes anterior y agregando luego la diferencia entre los empleados contratados y despedidos del mes actual. Recuerde que Employee Count no era un campo del archivo original.

Uso de Exists()

La función Exists() se utiliza a menudo con la cláusula Where en el script para cargar datos en caso de que los datos relacionados ya se hayan cargado en el modelo de datos.

En el ejemplo siguiente también usaremos la función Dual() para asignar valores numéricos a las cadenas.

Haga lo siguiente:

1. Cree una nueva app y asígnele un nombre.
2. Agregue una nueva sección de script en el Editor de carga de datos.
3. Denomine a la sección People.
4. Introduzca el siguiente script:

//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. Haga clic en Cargar datos.

En el script, los campos Age y AgeBucket se cargan solo si PersonID ya se ha cargado en el modelo de datos.

Observe que en la tabla AgeTemp hay edades enumeradas para PersonID 11 y 12 pero como esos ID no se cargaron en el modelo de datos (en la tabla People), son excluidos por la cláusula Where Exists(PersonID). Esta cláusula también puede escribirse así: Where Exists(PersonID, PersonID).

El resultado del script presentará el siguiente aspecto:



Si ninguno de los PersonID de la tabla AgeTemp se hubiera cargado en el modelo de datos, entonces los campos Age y AgeBucket no se habrían unido a la tabla People. Usar la función Exists() puede ayudar a prevenir registros/datos huérfanos en el modelo de datos, es decir, campos Age y AgeBucket que no tengan personas asociadas.

6. Cree una nueva hoja y asígnele un nombre.
7. Abra la nueva hoja y haga clic en Editar hoja.
8. Agregue una tabla estándar a la hoja con la dimensión AgeBucket y denomine a la app Grupos de edad.
9. Agregue un gráfico de barras a la hoja con la dimensión AgeBucket y la medida Count([AgeBucket]). Denomine a la visualización Number of people in each age group.
10. Ajuste las propiedades de la tabla y el gráfico de barras a sus preferencias y después haga clic en Hecho.

    Ahora debería tener una hoja similar a esta:

    

La función Dual() es muy útil en el script, o en una expresión de gráfico, cuando se necesita asignar un valor numérico a una cadena.

En el script superior, tiene una aplicación que carga edades, y usted ha decidido colocarlas en rangos para poder crear visualizaciones en función de los rangos de edades frente a las edades reales. Hay un rango para personas de menos de 25, entre 25 y 35, y así sucesivamente. Utilizando la función Dual(), los rangos de edad se pueden asignar a un valor numérico que, posteriormente, se puede usar para ordenar los rangos en un cuadro de lista o un gráfico. Por ello, como en la hoja de la app, la ordenación dice "Sin edad" al final de la lista.