Correspondências de intervalos e carregamento iterativo

O prefixo Intervalmatch de um comando LOAD ou SELECT é usado para vincular valores numéricos discretos a um ou mais intervalos numéricos. Esse recurso é muito útil que pode ser usado, por exemplo, em ambientes de produção.

Usando o prefixo IntervalMatch()

A correspondência de intervalo mais simples é quando você tem uma lista de números ou datas (eventos) em uma tabela e uma lista de intervalos em uma segunda tabela. O objetivo é vincular as duas tabelas. Em geral, este é um relacionamento de muitos para muitos, isto é, um intervalo pode ter muitas datas pertencentes a ele, e uma data pode pertencer a vários intervalos. Para resolver isso, é necessário criar uma tabela de ponte entre as duas tabelas originais. Existem duas formas de fazer isso.

A maneira mais simples de resolver este problema no Qlik Sense é usar o prefixo IntervalMatch() na frente de um comando LOAD ou SELECT. O comando LOAD/SELECT precisa conter apenas dois campos, os campos From e To, que definem os intervalos. Em seguida, o prefixo IntervalMatch() gerará todas as combinações entre os intervalos carregados e um campo numérico anteriormente carregado, especificado como o parâmetro do prefixo.

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 as seções como Events.

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

5. Carregue e, em seguida, selecione Events.txt.
6. Na janela Selecionar dados de, clique em Inserir script.
7. Carregue e, em seguida, selecione Intervals.txt.
8. Na janela Selecionar dados de, clique em Inserir script.
9. No script, nomeie a primeira tabela como Eventos e a segunda tabela como Intervals.
   
10. No final do script, adicione um IntervalMatch para criar uma terceira tabela que vinculará as duas primeiras tabelas:

BridgeTable:
IntervalMatch (EventDate)
LOAD distinct IntervalBegin, IntervalEnd
Resident Intervals;

11. Seu script deve ter a seguinte aparência:

Events:
LOAD
    EventID,
    EventDate,
    EventAttribute
FROM [lib://DataFiles/Events.txt] 
(txt, utf8, embedded labels, delimiter is '\t', msq);
 
Intervals:
LOAD
    IntervalID,
    IntervalAttribute,
    IntervalBegin,
    IntervalEnd
FROM [lib://DataFiles/Intervals.txt] 
(txt, utf8, embedded labels, delimiter is '\t', msq);
 
BridgeTable:
IntervalMatch (EventDate)
LOAD distinct IntervalBegin, IntervalEnd
Resident Intervals;

12. Clique em Carregar dados.
13. Abra o Visualizador do modelo de dados. O modelo de dados tem a seguinte aparência:



O modelo de dados contém uma chave composta (os campos IntervalBegin e IntervalEnd), que se manifestarão como uma chave sintética do Qlik Sense.

As tabelas básicas são:

• A tabela Events, que contém exatamente um registro por evento.
• A tabela Intervals, que contém exatamente um registro por intervalo.
• A tabela de ponte, que contém exatamente um registro por combinação de evento e intervalo e que vincula as duas tabelas anteriores.

Observe que um evento pode pertencer a vários intervalos se os intervalos forem sobrepostos. E um intervalo pode, obviamente, ter vários eventos pertencentes a ele.

Este modelo de dados é o melhor, no sentido de que ele é normalizado e compacto. As tabelas Events e Intervals não são alteradas e contêm o número original de registros. Todos os cálculos do Qlik Sense que são operados nessas tabelas, por exemplo, Count(EventID), funcionarão e serão avaliados corretamente.

Usando um loop While e um carregamento iterativo IterNo()

É possível obter quase a mesma tabela de ponte usando um loop While e IterNo() que cria valores enumeráveis entre os limites inferior e superior do intervalo.

Um loop dentro do comando LOAD pode ser criado com a cláusula While. Por exemplo:

Esse comando LOAD executará um loop em cada registro de entrada, carregando-os continuamente, enquanto a expressão na cláusula While for "true". A função IterNo() retorna “1” na primeira iteração, “2” na segunda e assim por diante.

Como você tem uma chave primária para os intervalos, IntervalID, a única diferença no script será o modo de criação da tabela de ponte:

Faça o seguinte:

1. Substitua os comandos Bridgetable existentes pelo seguinte script:

BridgeTable:
LOAD distinct * Where Exists(EventDate);
LOAD IntervalBegin + IterNo() - 1 as EventDate, IntervalID
    Resident Intervals
    While IntervalBegin + IterNo() - 1 <= IntervalEnd;

2. Clique em Carregar dados.
3. Abra o Visualizador do modelo de dados. O modelo de dados tem a seguinte aparência:



Em geral, a solução com três tabelas é a melhor, pois permite um relacionamento de muitos para muitos entre os intervalos e eventos. Mas uma situação comum é saber que um evento só pode pertencer a um único intervalo. Nesse caso, a tabela de ponte não é realmente necessária: O IntervalID pode ser armazenado diretamente na tabela de eventos. Existem várias maneiras de obter isso, mas a mais útil é unir Bridgetable com a tabela Events.

4. Adicione o script a seguir ao final do script:

Join (Events)
LOAD EventDate, IntervalID
Resident BridgeTable;
 
Drop Table BridgeTable;

5. Clique em Carregar dados.
6. Abra o Visualizador do modelo de dados. O modelo de dados tem a seguinte aparência:

Intervalos abertos e fechados

Um intervalo aberto ou fechado é determinado pelos pontos de extremidade, estejam eles incluídos no intervalo ou não.

• Se os pontos de extremidade estiverem incluídos, ele será um intervalo fechado:

[a,b] = {x ∈ ℝ ∣ a ≤ x ≤ b}

• Se os pontos de extremidade não estiverem incluídos, ele será um intervalo aberto:

]a,b[ = {x ∈ ℝ ∣ a < x < b}

• Se um único ponto de extremidade estiver incluído, ele será um intervalo semi-aberto:

[a,b[ = {x ∈ ℝ ∣ a ≤ x < b}

Nos casos em que os intervalos sejam sobrepostos e um número possa pertencer a mais de um intervalo, geralmente será necessário usar intervalos fechados.

No entanto, em alguns casos em que você não desejar ter intervalos sobrepostos, você desejará que um número pertença a apenas um intervalo. Dessa forma, ocorrerá um problema se um ponto for o fim de um intervalo e, ao mesmo tempo, o início do próximo. Um número com este valor será atribuído aos dois intervalos. Dessa forma, você desejará ter intervalos semi-abertos.

Uma solução prática para este problema é subtrair uma quantidade muito pequena do valor final de todos os intervalos, criando, assim, intervalos fechados, mas que não sejam sobrepostos. Se os números forem datas, a maneira mais simples de fazer isso é usar a função DayEnd(), que retorna o último milissegundo do dia:

Também é possível subtrair uma pequena quantidade manualmente. Se você fizer isso, certifique-se de que o valor subtraído não é muito pequeno, já que a operação será arredondada para 52 dígitos binários significativos (14 dígitos decimais). Se você usar uma quantidade muito pequena, a diferença não será significativa e você usará novamente o número original.