Usando Barras de Erros para mostrar metas individuais

Fala pessoal, tudo bom?

 

Sempre que me solicitam criar um gráfico para demonstrar o atingimento de metas individuais acabo fazendo o simples: uma gráfico combinado em que as barras são o realizado e os símbolos são as metas. Encontrei uma forma simples de demonstrar esse tipo de visualização através das Barras de Erros. Vamos aprender?

Continuar lendo

Anúncios

Cuidado com o uso do DISTINCT em união de tabelas

Fala pessoal, tudo bom?

É sempre bom conhecermos e entendermos (se possível rsrs) a fundo o funcionamento da ferramenta em que trabalhamos para evitarmos alguns erros.

Neste post quero tratar a função DISTINCT e suas pegadinhas!

Como sabemos, essa função vai eliminar linhas com registros iguais.

Por exemplo: Se tivermos uma tabela de cadastro de usuários com duas colunas: Numero e Nome, e o seguinte conteúdo:

DISTINCT_Tabela_Exemplo1

Ao realizar um select nesta tabela, teremos duas linhas com o número 1 e o nome Yuri, porém se fizermos um select com distinct, então essa tabela passará a ter somente três linhas, uma para YURI, outra para JULIAN e outra para o DAVID.

DISTINCT_Tabela_Exemplo2

Até aqui esta tudo normal!

Vamos complicar

Quando estamos desenvolvendo a modelagem dos dados, é muito provável que utilizamos o DISTINCT junto à um processo de união de tabelas, seja por CONCATENATE ou por JOIN. Isso é normal e faz parte do processo!

Vamos imaginar a seguinte situação:

DISTINCT_Tabela_Fato

Reparem que temos linhas repetidas, porém essas linhas não podem sumir ou nosso resultado ficará incorreto.

DISTINCT_Tabela_Fato_Com_Marcas

Neste caso temos:

Yuri: 900

Julian: 750

Queremos ligar essa tabela com a de Supervisor:

DISTINCT_Tabela_Supervisor

Nesta tabela as linhas 2 e 3 são idênticas.

Se fizermos o código tentarmos unir essa tabela por JOIN, conforme código abaixo:

DISTINCT_Script_Join_Tabelas

Nosso resultado será:

DISTINCT_Resultado_Join_Tabelas

Veja, Yuri 900, ok! Julian, 1500? Opa! Então o cadastro duplicado da tabela Supervisor duplicou os registros do Julian.

Simples, se o cadastro esta duplicado, então simplesmente irei fazer um DISTINCT nesta tabela e tudo se resolve, ufa!

DISTINCT_Script_Join_Tabelas_Distinct

Pronto, agora faço uma recarga e….

DISTINCT_Resultado_Join_Tabelas_ComDistinct

Eita… como assim Yuri com 700 e Julia 650? Os valores corretos não eram: Yuri 900 e Julian 750??? O que aconteceu?

O comportamento do DISTINCT na união de tabelas

Apesar do comportamento não ser o nosso esperado, ele é bem simples de entender: Usar o DISTINCT no mesmo processo de união de tabelas, eliminará TODAS as linhas resultantes que estiverem duplicadas, independente se o DISTINCT foi utilizado na primeira, na segunda ou na última tabela do processo, em outras palavras, o comportamento do DISTINCT será aplicado na tabela final que a união resultará.

Outro caso: Se você colocarmos o DISTINCT na primeira tabela? Teremos o mesmo resultado! Lembre-se, o DISTINCT terá efeito na tabela final (Tabela Fato) e não simplesmente na tabela em que colocamos o DISTINCT.

DISTINCT_Script_Join_Tabelas_Distinct2

Talvez você já tenha utilizado o DISTINCT no meio do processo de união de tabelas e nem percebeu que ele possui esse comportamento (BINGO!! Eu também não havia percebido até a semana passada!! rsrs).

Mas… e agora?

Calma! No nosso exemplo queremos remover apenas os registros duplicados da tabela Supervisor, então devemos criar uma passo temporário (fora do processo de união das tabelas Vendas e Supervisor) removendo as linhas iguais e depois, em outro processo, fazemos a união da tabela.

Ficaria mais ou menos assim:

DISTINCT_Script_Join_Tabelas_Script_Final

E o resultado:

DISTINCT_Resultado_Join_Tabelas_Final

Conclusão

É muito importante entendermos o comportamento de algumas funções dentro do QlikView e isso diminui bastante possíveis erros em nossos dados.

A utilização do DISTINCT sempre resultará na diminuição, e precisamos ser atentos aos nossos dados para esse problema não ocorra quando não queremos. Existem situações em que precisamos eliminar as linhas duplicadas, mas em outras não!

IMPORTANTE: Esse processo vai ocorrer seja por um JOIN ou um CONCATENATE!

Lembro de códigos que desenvolvi em que utilizei diversas vezes o DISTINCT (em várias tabelas diferentes) dentro de um processo de união, mas a partir de agora sei que não preciso, apenas um simples DISTINCT em alguma tabela do processo resolve! 🙂

Até a próxima semana pessoal!

Acumulado com Expressão

Fala pessoal! Tudo beleza?
 
Certamente vocês já fizeram algum relatório de Contabilidade e foi solicitado um indicador que possui os valores acumulados mês a mês, seja de Saldo, Lucro, etc.
 
Sabemos que a maioria dos gráficos possuem a função de acumular na aba de expressões, mas nem sempre esse acumulado vai atender a nossa demanda.
  
Gráficos que possuem a função de acumular: Barra, Linha, Combinado, Radar, Mekko, Dispersão, Grade e Table Simples.
 
Gráficos que não possuem a função de acumular: Mostrador, Tabela Dinâmica, Pizza, Funil e Blocos.
   
Imagine um cenário em que temos: um gráfico de tabela simples com o valor de REALIZADO e o valor de META. Precisamos agora criar mais três colunas: Realizado Acumulado, Meta Acumulada e % de Realizado em relação a meta.
 
Tranquilo! Gráfico de tabela simples possui a função de acumular a expressão por padrão.
  
Primeira expressão: Realizado Acumulado!
 
SUM(VALOR)
 
Marco Acumular, já era uma. 😀
  
Segunda expressão: Meta Acumulada!
 
SUM(META)
 
Marco Acumular, já era duas!
  
Terceira expressão: %!
 
Vou apenas reaproveitar as expressões…
  
[Realizado Acum.]/[Meta Acum.]
 
Pronto! Terminei!
   
Apenas verifico os valores só para ter certeza que não fiz nada errado
   
Jan/2015
Realizado Acum = 100
Meta Acum =  110
Percentual = 100/110 = 90,91%
Qlikview = 90,91% BOA!

   
Fev/2015
Realizado Acum = 220
Meta Acum =  240
Percentual = 220/240= 91,66%
Qlikview = 92,31% UÉ!
  
Bom, acho que preciso marcar o acumulado na expressão do percentual também!
 
Qlikview = 183,22%
 
NÃO É POSSÍVEL!
  
Deve ser algum erro de casa decimal (RSRSRS Eu sempre penso isso kkk!!!)
 
Refaço as contas e realmente não é erro de casa decimal.
 
Removo o acumulado das expressões: Realizado Acum e Meta Acum, e refaço o cálculo do percentual
  
Fev/2015
Realizado Acum = 120
Meta Acum =  130
Percentual = 120/130= 92,31%
  
Não acredito, o qlikview não consegue reutilizar a expressão com o acumulado. Afff! E agora?
  
Bom! Teremos então que investigar algumas funções que possam obter o mesmo resultado do acumulado do QlikView.
   

Funções

Hoje trabalharemos com as funções Funções Inter-Registro de Gráficos, são elas:
 
Above() = Acima – Retorna o valor da expressão avaliada com os valores de dimensão do gráfico da forma como aparecem na linha acima da atual em um segmento de coluna de uma tabela.
 
Below()Abaixo – Retorna o valor da expressão avaliado com os valores de dimensão do gráfico que aparecem na linha abaixo da atual dentro de um segmento de coluna em uma tabela.
 
Before() = Antes – Retorna o valor da expressão avaliada com os valores de dimensão de uma tabela dinâmica como aparecerem na coluna antes da atual.
 
After() = Depois – Retorna o valor da expressão avaliada com os valores de dimensão de uma tabela dinâmica como aparecerem na coluna depois da atual.
   

Características das funções

  • Na primeira coluna de um segmento de linha, um valor NULL será retornado, já que não há uma coluna
    antes dela.
  • Se a tabela for unidimensional ou se a expressão for precedida pelo qualificador total, o segmento de linha
    atual será sempre igual à linha inteira.
  • Especificar um deslocamento maior que 1 permite que você transfira a avaliação da expressão para essa outra coluna, por exemplo: Se usar para a função Before, então estará avaliando colunas a esquerda em relação a atual coluna.
  • Um número de deslocamento negativo tornará a função o seu inverso, por exemplo a função before ficará equivalente a função after.
  • A especificação de um deslocamento igual a 0 avaliará a expressão da coluna atual.
  • Ao especificar um terceiro parâmetro n maior que 1, a função retornará não um, mas uma série de valores n,
    um para cada n colunas da tabela. Dessa forma, a função pode ser usada como um argumento para qualquer uma das Funções de Intervalo de Gráfico especiais.

  
A parte acima grifada é muito importante, pois como queremos trabalhar com valores acumulados, então teremos que pegar o valor da linha/coluna atual e somar com o de linha(s)/coluna(s) anterior(es) e isso nos obriga a utilizar essas funções de intervalo de gráfico especiais.
  
Essas funções são precedidas da palavra Range, então temos: RangeSum, RangeAvg, RangeCount, RangeMin, RangeMax…. por aí vai.
  
IMPORTANTE: Essas funções de Range servirão para somar os resultados das funções Inter-Registros. Lembre-se que essas funções Inter-Registros retornaram vários resultados e é por isso que teremos que utilizar as funções de Range. Se tentássemos utilizar somente um SUM, o Qlikview retornaria nulo.
   

Exemplo

No exemplo em que vou comentar, estarei utilizando um gráfico de tabela simples com a dimensão MêsAno e suas respectivas expressões.
 
Se estamos trabalhando com um gráfico de tabela simples, então utilizaremos a função Inter-Registro Above(), pois o resultado do mês anterior esta na linha acima, desde que o gráfico esteja ordenado de forma Crescente.
 
Lembrando: Queremos acumular desde o primeiro MêsAno disponível nos filtros.
  
Expressão de Realizado: RangeSum(Above(SUM(VALOR), 0, RowNo()))
 
Vamos entender os parâmetros do Above
 
Deslocamento é zero, ou seja, estou avaliando a expressão da linha atual
 
N = RowNo() [RowNo() retorna o número da linha do gráfico]
 
Se estamos na primeira linha do gráfico, então a expressão estará da seguinte forma: RangeSum(Above(SUM(VALOR), 0, 1)), ou seja, estamos analisando somente a linha atual. Para a expressão RangeSum estamos somando o intervalo da linha atual até a linha atual.
 
Se estamos na segunda linha do gráfico, então a expressão estará da seguinte forma: RangeSum(Above(SUM(VALOR), 0, 2)), ou seja, estamos analisando a linha atual  e a linha anterior. Para a expressão RangeSum estamos somando o intervalo da linha atual até a linha anterior.
 
Se estamos na terceira linha do gráfico, então a expressão estará da seguinte forma: RangeSum(Above(SUM(VALOR), 0, 3)), ou seja, estamos analisando a linha atual  e duas linhas anteriores. Para a expressão RangeSum estamos somando o intervalo da linha atual até duas linhas anteriores.
  
Expressão de Meta: RangeSum(Above(SUM(META), 0, RowNo()))
 
A regra se mantém a mesma para a meta.
 
Agora o percentual fica fácil:
 
RangeSum(Above(SUM(VALOR), 0, RowNo())) / RangeSum(Above(SUM(META), 0, RowNo()))
 
Agora teremos o seguinte resultado
  
Grafico01
  
Muito bom!
   

Fique Atento

Lembre-se de escolher corretamente a expressão Inter-Registro a ser utilizada, se estiver em uma tabela dinâmica, com o MêsAno pivoteado, então temos que pegar o resultado da COLUNA ANTERIOR, para isso devemos utilizar a função Before().
 
Abaixo disponibilizei uma aplicação com um exemplo na tabela dinâmica e como acumular somente alguns passos, por exemplo: Somente 1 mês anterior ou somente dois meses anteriores.
 
Para fazer download da aplicação de exemplo clique aqui!
   

Conclusão

Se o que você deseja fazer o QlikView oferece, então utilize! Sempre as funções prontas do QlikView oferecem melhor performance, mas nem tudo o QlikView nos oferece de “mão beijada”, desta forma é bom estarmos preparados para essas ocasiões.
 
Até a próxima semana!

IntervalMatch Estendido na Prática

Fala pessoal, tudo bom?
 
Semana passada abordamos o tema de intervalos e descobrimos que o QlikView possui uma função própria para se tratar desses casos. Não viu? Clique aqui!

   

Definições do IntervalMatch

Também quero aproveitar para relembrar algumas definições do IntervalMatch:

  • Antes do comando intervalmatch, o campo que contém os pontos de dados discretos (“Nota” no exemplo do post anterior) já deve ter sido lido no QlikView. O próprio comando intervalmatch não lê esse campo a partir da tabela da base de dados.
  • A tabela lida no comando intervalmatch deve sempre conter exatamente dois campos (“Min” e “Max” no exemplo do post anterior). Para estabelecer um link com outros campos, é necessário ler os campos de intervalo com campos adicionais em um comando load ou select separado.
  • Os intervalos estão sempre fechados, isto é, sempre contêm pontos de extremidade.
  • Os limites não numéricos fazem com que o intervalo seja desconsiderado (indefinido)
  • Os limites nulos (NULL) estendem o intervalo indefinidamente (ilimitado).
  • Os intervalos podem estar sobrepostos, ou seja, posso ter dois intervalos que contenham valores repetidos, esses valores estarão vinculados a todos os intervalos correspondentes.

   

Caso a utilizar IntervalMatch Estendido

Essa semana iremos tratar de um intervalo que não depende simplesmente do intervalo de valores, mas também depende de um segundo campo que define a qual intervalo estamos tratando. Podemos chamar esse segundo intervalo de campo chave.

  

Resgatando o exemplo da semana passada em que tínhamos uma tabela com a classificação das notas e outra com as notas recebidas por cada aluno, vamos adicionar uma nova coluna com o nome da escola. Imagine que o nosso professor ministre aulas em duas escolas diferentes e cada uma dessas escolas possuem a sua própria classificação das notas.

  

Na escola Puríssimo a classificação é:

  • de 0 até 34 é Nota “E”
  • de 35 até 40 é Nota “D”
  • de 41 até 60 é Nota “C”
  • de 61 até 80 é Nota “B”
  • de 81 até 100 é Nota “A”

  

Na escola Sesi a classificação é:

  • de 0 até 15 é Nota “E”
  • de 16 até 40 é Nota “D”
  • de 41 até 60 é Nota “C”
  • de 61 até 70 é Nota “B”
  • de 71 até 100 é Nota “A”

  

Como temos classificações diferentes em duas escolas (CHAVE) diferente, então a nossa tabela de notas deve possuir a qual escola (CHAVE) aquela nota (do aluno) pertence.

   

Prática

Em primeiro lugar crie a nossa tabela com os intervalos.

  

Grade:
LOAD * INLINE [
Escola, Min, Max, Grade
Escola Puríssimo, 0, 34, E
Escola Puríssimo, 35, 40, D
Escola Puríssimo, 41, 60, C
Escola Puríssimo, 61, 80, B
Escola Puríssimo, 81, 100, A
Escola Sesi, 0, 15, E
Escola Sesi, 16, 40, D
Escola Sesi, 41, 60, C
Escola Sesi, 61, 70, B
Escola Sesi, 71, 100, A
];

  

Agora vamos criar a nossa tabela das notas por escola e aluno.

Notas:
LOAD * INLINE [
Escola, Nota, Aluno
Escola Puríssimo, 30, Yuri
Escola Puríssimo, 50, Edson
Escola Puríssimo, 99, Alan
Escola Puríssimo, 10, Geraldo
Escola Puríssimo, 93, Henrique
Escola Sesi, 27, Christian
Escola Sesi, 44, Michele
Escola Sesi, 80, Roberto
Escola Sesi, 76, Ivanir
Escola Sesi, 98, Zé
];

  

Note: A coluna “Escola” é a chave que define a nota e a classificação, sem ela não saberíamos informar a classificação da nota desse aluno, pois agora temos dois intervalos diferentes na mesma tabela.

  

Agora vamos para a utilização do IntervalMatch Estendido

A utilização do IntervalMatch Estendido deve-se partir da leitura da tabela de intervalos e o comando IntervalMatch deve-se relacionar com a coluna que deve ser checada dentro do intervalo e a coluna de chave:

Usando_IntervalMatch_Estendido:
IntervalMatch(Nota,Escola) //Comando IntervalMatch chamando a coluna a ser checada dentro do Intervalo e a chave para definir a qual intervalo o valor se refere.

Usando_IntervalMatch_Estendido:
IntervalMatch(Nota,Escola)  //Comando IntervalMatch chamando a coluna a ser checada dentro do Intervalo e a coluna de //chave das classificações
LOAD
Min, //Primeiro o menor valor do intervalo
Max, //depois o maior valor do intervalo
Escola //Por último a coluna chave.
RESIDENT Grade; //Tabela de Intervalos

  

Com o resultado agora teremos a tabela “Usando_IntervalMatch_Estendido” que possui as colunas: Min, Max, Nota e Escola. Essa tabela já fez a ligação entre as tabelas Nota e Grade, veja:

  

IntervalMatch Estendido 01

  

Agora é só fazer o JOIN necessário para remover essa chave sintética. Remova a parte do IntervalMatch e faça a alteração abaixo:

  

LEFT JOIN (Notas)

IntervalMatch(Nota,Escola) //Comando IntervalMatch chamando a coluna a ser checada dentro do Intervalo e a coluna de chave das classificações
LOAD
Min, //Primeiro o menor valor do intervalo
Max, //depois o maior valor do intervalo
Escola //Por último a coluna chave.
RESIDENT Grade; //Tabela de Intervalos

LEFT JOIN (Notas)

LOAD
*
RESIDENT Grade;

DROP TABLE Grade;
DROP Fields Min, Max;

   

Resultado final

IntervalMatch Estendido 02
Download da aplicação de exemplo!

   

Conclusão

Esse é um caso que mais ocorre em nosso dia a dia em relação ao caso do post anterior, pois dificilmente teremos apenas um intervalo de dados para checar.

E reforço o texto utilizado no post anterior: É uma boa prática conhecermos muitas das funções que o QlikView nos oferece, pois estas funções sempre irão nos poupar linhas e linhas de código.

  

Até a próxima semana pessoal!

IntervalMatch na prática

Fala galera, tudo bom?

Já se depararam com uma situação em que era necessário fazer um JOIN entre tabelas, porém esse JOIN não é o valor exato da segunda tabela e sim um valor que esta dentro de um intervalo dela?

Por exemplo:

Tenho uma tabela com os limites das notas de uma escolha

  • de 0 até 34 é Nota “E”
  • de 35 até 40 é Nota “D”
  • de 41 até 60 é Nota “C”
  • de 61 até 80 é Nota “B”
  • de 81 até 100 é Nota “A”

A nossa segunda tabela possui as notas dos alunos.

Nem sempre teremos uma nota com o valor dos limites do intervalo e isso impossibilita de resolvermos essa situação utilizando um comando de JOIN.

Podemos criar os registros que estão faltando no intervalo e depois é só fazer Join? Sim, para esse exemplo pode até funcionar, pois no total teremos 100 registros, mas e se a nossa tabela de “Range” possuir intervalos de milhares? milhões? Acho que para esses casos não seria uma boa solução, concordam?
   

IntervalMatch

O prefixo IntervalMatch é usado para criar uma tabela comparando valores numéricos discretos com um ou mais intervalos numéricos.
Ele deve ser colocado antes do Load ou declaração de Select (SQL) que carrega os intervalos. O campo que contém os pontos de dados discretos (Notas no exemplo abaixo) já deve ter sido carregado no QlikView antes do comando com o prefixo IntervalMatch. O prefixo não lê esse campo a partir da tabela da base de dados. O prefixo transforma a tabela carregada de intervalos em uma tabela que contém uma coluna adicional: os pontos de dados numéricos discretos Ele também expande o número de registros de forma que a nova tabela tenha um registro por combinação possível de ponto de dados discreto e intervalo.
Os intervalos podem estar sobrepostos e os valores discretos estarão vinculados a todos os intervalos
correspondentes.
   

Prática

Em primeiro lugar crie a nossa tabela com os intervalos.

Grade_Notas:
LOAD * INLINE [
Min, Max, Grade
0, 34, E
35, 40, D
41, 60, C
61, 80, B
81, 100, A
];

Agora vamos criar a nossa tabela das notas por aluno

Notas:
LOAD * INLINE [
Nota, Aluno
30, Yuri
50, Edson
99, Alan
10, Geraldo
93, Henrique
];

Agora vamos para a utilização do IntervalMatch

A utilização do IntervalMatch deve-se partir da leitura da tabela de intervalos e o comando IntervalMatch deve-se relacionar com a coluna que deve ser checada dentro do intervalo:

Usando_IntervalMatch:
IntervalMatch(Nota) //Comando IntervalMatch chamando a coluna a ser checada dentro do Intervalo
LOAD
Min, //Primeiro o menor valor do intervalo
Max //depois o maior valor do intervalo
RESIDENT Grade; //Tabela de Intervalos

Como resultado agora teremos a tabela “Usando_IntervalMatch” que possui as colunas: Min, Max e Nota. Essa tabela já fez a ligação entre as tabelas Nota e Grade, veja:

IntervalMatch 01

Agora é só fazer o JOIN necessário para remover essa chave sintética. Remova a parte do IntervalMatch e faça a alteração abaixo:

LEFT JOIN (Notas)

IntervalMatch(Nota) //Comando IntervalMatch chamando a coluna a ser checada dentro do Intervalo
LOAD
Min, //Primeiro o menor valor do intervalo
Max //depois o maior valor do intervalo
RESIDENT Grade; //Tabela de Intervalos

LEFT JOIN (Notas)

LOAD
*
RESIDENT Grade;

DROP TABLE Grade;
DROP Fields Min, Max;
   

Resultado final

IntervalMatch 02
A aplicação de exemplo pode ser baixada aqui!
   

Conclusão

É uma boa prática conhecermos muitas das funções que o QlikView nos oferece, pois estas funções sempre irão nos poupar linhas e linhas de código. A utilização do IntervalMatch também é o melhor recurso para essa situação, garantindo o resultado esperando com uma ótima performance na recarga do script.

Na próxima semana irei demonstrar como utilizar o IntervalMatch Estendido 😀

Até a próxima semana!