Usando funções de inter-registro: Peek, Previous e Exists

Estas funções são usadas quando um valor dos registros de dados anteriormente carregados é necessário para a avaliação do registro atual.

Nesta parte do tutorial, analisaremos as funções Peek(), Previous() e Exists().

Peek()

Peek() retorna o valor de um campo em uma tabela para uma linha que já foi carregada. O número da linha pode ser especificado, assim como a tabela. Se nenhum número de linha for especificado, o último registro carregado anteriormente será usado.

Sintaxe:  

A linha deve ser um número inteiro. 0 representa o primeiro registro, 1 o segundo e assim por diante. Números negativos indicam a ordem no final da tabela. -1 representa o último registro lido.

Se nenhuma linha for definida, -1 será exibido.

Tablename é um rótulo de tabela sem os dois-pontos finais. Se nenhum tablenamefor definido, a tabela atual será assumida. Se usado fora do comando LOAD ou em referência a outra tabela, o tablename deve ser incluído.

Previous()

Previous() encontra o valor da expressão expr usando dados do registro de entrada anterior que não foi descartado devido a uma cláusula where. No primeiro registro de uma tabela interna, a função retornará NULL.

Sintaxe:  

A função Previous() pode ser inserida para acessar os registros anteriores. Os dados são buscados diretamente da fonte de entrada, o que permite fazer referência também aos campos não carregados no Qlik Sense, ou seja, mesmo que não tenham sido armazenados no banco de dados associado.

Exists()

Exists() determina se um valor de campo específico já foi carregado no campo no script de carga de dados. A função retorna TRUE ou FALSE, de forma que pode ser usada na cláusula where de um comando LOAD ou um comando IF.

Sintaxe:  

O campo deve existir nos dados carregados até o momento pelo script. Expression é uma expressão avaliada para o valor de campo a ser pesquisado no campo especificado. Se for omitida, será assumido o valor do registro atual no campo especificado.

Usando o Peek() e Previous()

Na sua forma mais simples, Peek() e Previous() são usados ​​para identificar os valores específicos em uma tabela. Eis uma amostra dos dados na tabela Employees que você carregará neste exercício.

Atualmente, ela só coleta dados para o mês, contratações e demissões; por isso, adicionaremos campos para Employee Count e Employee Var usando as funções Peek() e Previous() para ver a diferença mensal no total de funcionários.

Faça o seguinte:

1. Abra o aplicativo Tutorial de uso de scripts avançado.
2. Adicione uma nova seção de script no Editor de carregamento de dados.
3. Nomeie a seção como Employees.

4. Em DataFiles no menu direito, clique em Selecionar dados.

5. Carregue e, em seguida, selecione Employees.xlsx.
Nota informativaEm Field names, certifique-se de que Embedded field names esteja selecionado para incluir os nomes de campos da tabela ao carregar os dados.
6. Na janela Selecionar dados de, clique em Inserir script.

Seu script deve ter a seguinte aparência:

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

7. Modifique o script para que ele agora tenha a seguinte aparência:

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

As datas do campo Date na pasta do Excel estão no formato MM/DD/AAAA. Para assegurar que as datas sejam interpretadas corretamente usando o formato das variáveis do sistema, a função Date é aplicada ao campo Date.

A função Peek() permite identificar qualquer valor carregado para um campo definido. Na expressão, examinamos primeiro para ver se rowno() é igual a 1. Se for igual a 1, nenhum Employee Count existirá e, portanto, preencheremos o campo com a diferença de Hired menos Terminated.

Se rowno() for maior do que 1, observaremos a Employee Count do último mês e usaremos esse número para somá-lo à diferença dos funcionáriosHired menos funcionários Terminated.

Observe também que, na função Peek(), usamos um (-1). Isto informa o Qlik Sense para examinar o registro acima do registro atual. Se (-1) não for especificado, o Qlik Sense considerará que você deseja examinar o registro anterior.

8. Adicione o seguinte ao final do 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];

A função Previous() permite identificar o último valor carregado para um campo definido. Na expressão, primeiro examinamos para ver se rowno() é igual a 1. Se for igual a 1, sabemos que não haverá Employee Var porque não há nenhum registro para o mês anterior Employee Count. Portanto, basta digitar 0 para o valor.

Se rowno() for maior que 1, saberemos que haverá um Employee Var; portanto, examinaremos a Employee Count do último mês e subtrairemos esse número da Employee Count do mês atual para criar o valor no campo Employee Var.

Seu script deve ter a seguinte aparência:

[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. Clique em Carregar dados.

Em uma nova pasta na visão geral do aplicativo, crie uma tabela usando Date, Hired, Terminated, Employee Count e Employee Var como as colunas da tabela. A tabela resultante deve ficar assim:

Peek() e Previous() permitem visar linhas definidas em uma tabela. A maior diferença entre as duas funções é que a função Peek() permite que o usuário examine um campo que não tenha sido anteriormente carregado no script, enquanto que a função Previous() só permite examinar um campo anteriormente carregado. Previous() opera na entrada do comando LOAD, enquanto que Peek() opera na saída do comando LOAD. (O mesmo que a diferença entre RecNo() e RowNo()). Isso significa que as duas funções se comportarão de forma diferente se você tiver uma cláusula Where.

Portanto, a função Previous() seria mais adequada quando você precisar mostrar o valor atual em relação ao valor anterior. No exemplo, foi calculado a variação de funcionários em cada mês.

A função Peek() seria mais adequada para quando você estiver visando um campo que não tenha sido anteriormente carregado na tabela ou quando você precisar visar uma linha específica. Isso foi mostrado no exemplo em que calculamos a Employee Count examinando a Employee Count do mês anterior e somando a diferença entre os funcionários contratados e desligados no mês atual. Lembre-se de que Employee Count não era um campo do arquivo original

Usando Exists()

A função Exists() é geralmente usada com a cláusula Where no script para carregar dados se os dados relacionados já tiverem sido carregados no modelo de dados.

No exemplo a seguir, também usamos a função Dual() para atribuir valores numéricos a cadeias.

Faça o seguinte:

1. Crie um novo aplicativo e nomeie-o.
2. Adicione uma nova seção de script no Editor de carregamento de dados.
3. Nomeie a seção como People.
4. Digite o seguinte 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. Clique em Carregar dados.

No script, os campos Age e AgeBucket são carregados apenas se PersonID já tiver sido carregado no modelo de dados.

Observe na tabela AgeTemp que existem idades listadas para PersonID 11 e 12, mas já que estes IDs não foram carregados no modelo de dados (na tabela People), eles são excluídos pela cláusula Where Exists(PersonID). Esta cláusula também pode ser escrita da seguinte forma: Where Exists(PersonID, PersonID).

A saída do script tem a seguinte aparência:



Se nenhum PersonID da tabela AgeTemp tiver sido carregado no modelo de dados, os campos Age e AgeBucket não teriam sido unidos à tabela People. Usar a função Exists() pode ajudar a prevenir registros/dados órfãos no modelo de dados, ou seja, os campos Age e AgeBucket que não têm nenhuma pessoa associada.

6. Crie uma nova pasta e nomeie-a.
7. Abra a nova pasta e clique em Editar pasta.
8. Adicione uma tabela padrão à pasta com a dimensão AgeBucket e nomeie a visualização como Grupos de idade.
9. Adicione um gráfico de barras à pasta com a dimensão AgeBucket e a medida Count([AgeBucket]). Nomeie a visualização como Number of people in each age group.
10. Ajuste as propriedades da tabela e do gráfico de barras de acordo com suas preferências e clique em Concluído.

    Sua pasta deve ter a seguinte aparência:

    

A função Dual() é útil no script ou em uma expressão do gráfico quando há a necessidade de atribuir um valor numérico a uma cadeia de caracteres.

No script acima, existe um aplicativo que carrega as idades, e você decidiu colocar essas idades em compartimentos para poder criar visualizações com base nos compartimentos de idades em relação às idades reais. Existe um compartimento para as pessoas com menos de 25, entre 25 e 35 e assim por diante. Ao usar a função Dual(), um valor numérico pode ser atribuído aos compartimentos de idades, que posteriormente pode ser usado para classificar os compartimentos de idades em uma caixa de lista ou em um gráfico. Portanto, assim como ocorre na pasta do aplicativo, a classificação insere "Nenhuma idade" no fim da lista.