Campos Data/Hora no QlikView
1.1 Funções de Interpretação
1.2 Funções de Formatação
Na maior parte das vezes o trabalho com campos contendo data/hora não implica em qualquer esforço adicional, pois o QlikView é suficientemente "inteligente" para tratar as diversas formatações potencialmente existentes. Geralmente, o trabalho com campos contendo data/hora são transparentes ao elaborador do painel e passam desapercebidos. No entanto, em certas circunstâncias campos com datas não são interpretados adequadamente, de maneira que algum esforço adicional é necessário em nível de script.
Antes de trabalhar com campos do tipo data ou hora, é preciso entender como o QlikView interpreta os valores destes campos. Todos que possuem alguma experiência com ferramentas tradicionais perguntam quais são os tipos de dados que o QlikView possui. Na realidade, não existem tipos de dados diferentes no QlikView. Ao contrário das soluções tradicionais, o Qlik utiliza uma técnica de armazenamento dobrado, chamado de Dual. Ou seja, todo valor de um campo possui uma representação textual e, sempre que possível, uma representação numérica.
Se um valor de um campo puder ser representado como um número, então este número será armazenado na parte numérica do armazenamento Dual, enquanto a representação textual será armazenada na parte texto do armazenamento. Portanto, um único aparente valor pode ter duas partes armazenadas. Assuma, por exemplo, o seguinte fragmento de carga.
42.498];
Observando os valores exibidos em uma lista é possível notar que o alinhamento é automaticamente ajustado a direita. Os valores são considerados numéricos e por isso pode-se crer que o QlikView classificou o campo inteiro como número. Mas na prática, o campo SomeValue possui dois conjuntos de dados, um representando a parte numérica e outro representando a parte textual, pois o campo é na verdade Dual (armazenamento duplo). O inverso também é verdadeiro. Ou seja, campos podem ter conteúdo textual que são mapeáveis para valores numéricos. Por exemplo, um campo com os valores Jan, Fev, Mar podem ter seus respectivos valores numéricos.
No caso de datas, valores textuais podem estar armazenados do lado textual do conteúdo Dual e ainda manter os respectivos valores numéricos no lado numérico do tipo de dados. Isso significa que ao ordenar um campo que contenha valores de data interpretáveis, o conteúdo poderá ser ordenado pela parte numérica do campo, não pela textual. Note o seguinte exemplo.
Months, NumericMonth
abr, 4];
MonthTestDual:
LOAD Dual(Months, NumericMonth) As NewMonthName Resident MonthTest;
Como resultado da primeira instrução LOAD (exibida na 1ª figura a direita), a lista exibe o conteúdo com alinhamento a esquerda em ordem alfabética, mesmo sem nenhuma configuração de ordenação aplicada. Já a segunda instrução utiliza a função Dual para arbitrariamente designar o valor numérico do campo, permanecendo com o conteúdo textual do lado texto do tipo de dado. Por isso, apesar da segunda lista apresentar os dados textuais, a ordenação é mantida com base nos valores numéricos que não são exibidos. Note! Não são dois campos na segunda lista, mas um único campo com duas partes, uma textual e outra numérica.
Alterar a formatação alterará a forma de apresentação do conteúdo da segunda lista, dando a impressão de que os dados são outros. Por exemplo, acessando as propriedades da lista, na guia Número, é possível alterar a formatação para Number e os dados apresentados serão numéricos refletindo a parte não textual do conteúdo. Ou seja, cada valor da coluna está representado por dois valores armazenados no QlikView, um textual e outro numérico.
Por isso existem dois tipos diferentes de funções no QlikView, uma para Interpretação e outra para Formatação. Todas as funções de Interpretação são aquelas que terminam com cerquilha (#), tal como Date#, TimeStamp#, etc. convertendo a parte textual do Dual para numérico. Estas funções (interpretação) fazem a leitura de textos e os transformam para os respectivos valores numéricos. Na prática, converte o lado textual do Dual para o lado numérico.
Usando desta lógica fica simples compreender que para converter o texto para o respectivo número que corresponde a uma data, basta utilizar a relação das funções que possuem cerquilha. Por exemplo, para interpretar o campo Months na tabela MonthTest basta utilizar a função Date# especificando o formato de entrada.
InterpretationFunctions:
abr];
LOAD Date#(MonthsName, 'MMM') As MonthName1 Resident InterpretationFunctions;
Note que o campo MonthsName da tabela InterpretationFunctions contém originalmente apenas textos. Isso é tão verdade que ao adicionar uma lista para a interface o alinhamento do texto ficará a esquerda. Porém, ao usar a função Date# o texto é interpretado e transformado em números que correspondem aos valores numéricos de cada mês. Por isso, quando o script é recarregado o texto é automaticamente alinhado a direita, pois a gravação numérica força o ajuste demonstrando que no campo há valores numéricos.
Nota:  Por ser Dual um campo no QlikView pode conter valores numéricos e textuais ao mesmo tempo.
Como visto na seção anterior, funções de interpretação fazem a leitura de valores textuais para preencher o lado numérico do tipo de dado no QlikView, o Dual. As funções de interpretação são aquelas que possuem o sinal de cerquilha no final do nome.
Num#: Converte um texto com pontos e vírgulas para um valor propriamente numérico de acordo com o formato informado.
Money#: Converte um texto para moeda de acordo com o padrão do formato informado.
Date#: Converte um texto para uma data de acordo com o padrão de formatação informada.
Time#: Converte um texto para hora (numericamente falando) de acordo com um padrão de formatação informada.
Timestamp#: Converte um texto para um conteúdo numérico de data & hora de acordo com o padrão de formato informado.
Interval#: Converte um texto em valores temporais numéricos superiores ao padrão de horas 23:59:59.
A seguir são apresentados alguns cenários do uso de funções de Interpretação.
Date#(DateField, 'M/D/YY') As Date
Esta expressão utiliza a função Date# para ler o campo DateField que não foi automaticamente interpretado como data pelo QlikView, possivelmente porque a formatação está diferente do padrão do sistema operacional e da variável de ambiente DateFormat, definida no início da execução do script. Por exemplo, o padrão de interpretação de datas em Português-BR na variável DateFormat é 'DD/MM/YYYY', de forma que outros formatos precisam ser indicados para que possam ser interpretados como números que representam datas.
Month(Date#(Month,'MMM')) As Month
Muito semelhante aos exemplos mostrados na Introdução, o uso da função Date# acima interpreta o campo Month assumindo que haja textos equivalentes aos nomes de cada mês dentro do campo, em forma textual. Como datas e horas são representações de números inteiros e decimais, respectivamente, o texto é interpretado pela função para preencher o lado numérico do tipo de dados Dual. Vale lembrar que o parâmetro MMM é case-sensitive, de maneira que M e m representam informações diferentes. Enquanto M em maiúsculo indica meses escritos de forma textual, m em minúsculo indica minutos.
Date(Date#(DateField, 'M/D/YY'), 'YYYY-MM-DD') As Date
Funções podem ser utilizadas de maneira aninhada. Ou seja, uma dentro de outra. Funções de interpretação (que usam a cerquilha) podem fazer a leitura adequada do valor textual para transformá-lo em número (data), enquanto a função Date (sem cerquilha) transforma o dado numérico do Dual em texto. Por isso, no exemplo acima a função Date# é utilizada para converter um texto não interpretado como data pelo QlikView. Em seguida, a função Date sem cerquilha formata a apresentação do número de forma textual. Outro exemplo de funções aninhadas pode ser observado a seguir.
Na figura ao lado é possível observar que o campo Date foi inicialmente fornecido pela fonte de dados como uma sequencia de números que representam o ano em formato de 4 dígitos, seguido pelo mês com dois dígitos e finalmente pelo dia também com dois dígitos. Por ser uma cadeia de números o QlikView não consegue interpretar o conteúdo do campo como data e o uso de funções de interpretação se faz necessário. A segunda coluna utiliza da função Date# para ler o campo Date no formato YYYYMMDD, onde Y representa Ano, M indica Mês e D representa dia. O resultado é o valor numérico correto que representa cada data. Ou seja, 04-ABRIL-2012 é, internamente, 41003.
É preciso lembrar, no entanto, que cada valor de um registro dentro de uma coluna no QlikView é representado por dois valores internos. Um do lado numérico e outro do lado textual. Por isso, ao converter o valor original de Date utilizando a função Date# a coluna foi preenchida com o valor numérico. Para transformar este valor em algo legível é preciso utilizar funções de Formatação, que fazem a leitura do lado numérico para o lado textual de Dual. Isso é feito utilizando o resultado da conversão através da função Date, sem cerquilha.
Como visto, funções com cerquilha são aquelas destinadas a fazer a leitura da parte textual de um campo Dual na parte numérica. O inverso também ocorre com funções sem cerquilha, consideradas funções de formatação. Na prática, uma função de formatação é destinada a ler a parte numérica do conteúdo de Dual e transpor para a parte textual aplicando uma certa formatação. Existem muitas funções de formatação, sendo as mais utilizadas mencionadas a seguir:
MakeDate: Utilizada quando os valores de mês, ano e dia estão armazenados em diferentes campos em valores numéricos.
Time: Utiliza um número para transformá-lo em formato de tempo (horas/minutos/segundos).
Day: Faz a leitura do lado numérico da data e apresenta o dia correspondente.
Month: Faz a leitura do lado numérico da data e apresenta o mês correspondente.
Year: Faz a leitura do lado numérico da data e apresenta o ano correspondente.
MonthName: Faz a leitura do lado numérico da data e apresenta no formato mês seguido do ano.
QuarterName: Faz a leitura do lado numérico da data em formato do mês inicial e final do trimestre do ano.
DayName: Faz a leitura do lado numérico da data em formato dia, mês, arredondando a data/hora apenas para data.
LOAD Day(Now()) AutoGenerate 1;
LOAD Month(Now()) AutoGenerate 1;
LOAD Year(Now()) AutoGenerate 1;
LOAD MonthName(Now()) AutoGenerate 1;
LOAD QuarterName(Now()) AutoGenerate 1;
LOAD DayName(Now()) AutoGenerate 1;
Nota:  A instrução AutoGenerate cria o número de linhas necessárias informada pelo parâmetro a frente. No caso, 1.
Funções de formatação de data podem ser combinadas com funções numéricas para diferentes resultados. Imagine um campo possua datas e horas e se deseja manter apenas as datas, sem horas. O valor de 01/01/2012 18:00:00 corresponde a 40909,75, onde a parte inteira representa a data e a parte decimal representa a hora. Por isso, se o objetivo é retirar as horas, basta arredondar o valor apresentado desprezando as decimais.
Date(Floor(Now()), 'DD-MM-YYYY') As Date
A função Floor arredonda um número com decimal para inteiro. Já a função Date (sem cerquilha) transforma o resultado numérico inteiro para o formato legível de data no formato DD-MM-YYYY. O inverso também é verdadeiro. É possível obter de um campo data/hora apenas a parte da hora com minutos e segundos. Neste caso, basta o uso da função Frac que retorna apenas a fração do número passado como parâmetro.
Time(Frac(Timestamp#(Now(), 'YYYY-MM-DD hh:mm:ss')), 'hh:mm:ss') As CreatedTime
Campos e variáveis são Dual, o que significa que cada valor é armazenado em número e texto simultaneamente. Quando houver comparações de variáveis com campos data é mais simples armazenar o conteúdo como número ao invés de texto formatado. Isso significa que sempre que for armazenar uma data em uma variável é importante interpretá-la como número. Por exemplo:
Let vAMonthAgo = Num(AddMonths(Today(), – 1);
Utilizando a função Num a variável hospedará a parte numérica do Dual. Isso simplifica o uso em comparações como a apresentada a seguir:
Where DeliveryDate > $(#vAMonthAgo) and DeliveryDate <= $(#vToday);
Nota: O uso do sinal de cerquilha (#) no início do nome da variável, faz com que o Qlik interprete valores decimais
com ponto ao invés de vírgula. Especialmente útil para países como Brasil onde o decimal é virgula.
Utilize as funções de data/hora para construir um calendário de períodos completos.
Desta maneira é possível apresentar filtros para o usuário sem que haja lacunas de meses, dias, dias da semana, etc. Veja aqui como criar o calendário!