Cuidado com a leitura de múltiplos arquivos em união de tabelas (JOIN)

Fala pessoal, tudo bom?

Mais um post sobre os CUIDADOS que devemos ter em nosso script para evitar erros!

Dessa vez quero falar sobre a leitura de múltiplos arquivos através dos caracteres curinga* (ahn? ajuda aí!)

Wikipedia diz: Em computação os caracteres-curinga são utilizados em casamento de padrões para substituir algum outro carácter desconhecido em uma sequência de caracteres.

É muito utilizado ao realizar um glob para procurar arquivos cujo nome ou caminho completo são desconhecidos. Nos interpretadores de comandos, o caractere asterisco (*) é reconhecido como um caractere-curinga que casa com qualquer número de caracteres desconhecidos e o caractere interrogação (?) é um curinga que casa com um único caractere desconhecido.

(entendido? rsrs! retornando ao texto) quando utilizamos em conjunto de união de tabelas (JOIN! – Neste exemplo o CONCATENATE não é um erro, mas falo disso no final).

É um erro bobo, mas fácil de ser cometido. Sabe por quê? Porque conhecemos os conceitos e comportamentos do QlikView.

Conceitos e comportamentos

Sabemos que o QlikView possui o comportamento de unir (transformar em apenas uma) tabelas que possuem os mesmos campos (com a mesma nomenclatura), por esta razão quando utilizamos o caractere curinga para fazer uma leitura múltipla de arquivo, o QlikView “faz o favor” de unir todas essas tabelas (desde que respeite a regra anterior que falamos) em apenas uma.

Exemplo

Imagine um cenário com em que precisamos carregar planilhas com notas fiscais. Cada uma das planilhas possui informação de apenas um mês/ano.

Lista de arquivos: NF_jan12.xls, NF_fev12.xls, NF_mar12.xls e NF_abr12.xls.

Esses arquivos estão perfeitos para serem carregados de uma única vez através do caractere curinga “*”.

Ah, vale lembrar que esses arquivos estão todos do mesmo padrão, isso inclui: nome das colunas e nome da aba a serem carregadas.

Em nosso script do qlikview:

01

Resultado:

02

A leitura dos quatros arquivos se resumiram em apenas uma tabela devido ao comportamento do QlikView.

Agora vamos para o exemplo em que podemos se esquecer desse comportamento e cometer um erro grave.

O problema

Agora temos uma tabela principal com o nome das empresas de um grupo, conforme imagem:

03

Queremos exibir os dados de faturamento, mas mostrando todas as empresas, ou seja, mesmo que a empresa não tenha faturado nada, esta deve exibir no relatório.

Tecnicamente, iremos carregar primeira a tabela com a descrição das empresas e depois fazer o LEFT JOIN utilizando a tabela de nota fiscal. Nosso script seria:

04

Executando o script, veja o resultado:

05 06

O resultado ficou como esperado! As empresas 4 e 5 estão sendo exibidas sem nenhuma nota fiscal e o restante das informações fez a ligação com a descrição da empresa.

Opa, mas peraí! Carreguei informações de Jan/2013, Fev/2013, Mar/2013 e Abr/2013… Por quê esta aparecendo somente os dados de Abr/2013?

O erro cometido

Aparentemente fizemos tudo certo, mas um grande detalhe de comportamento nos atrapalhou: leitura múltipla de arquivos em conjunto com união de tabelas.

Vamos pensar…

Seguindo o comportamento de execução do Qlikview, em primeiro lugar carregamos a tabela Empresas e passamos a chama-la de Fato, tendo essa tabela carregada (em memória), realizamos a leitura múltipla das tabelas de notas fiscais e fazemos a união desta tabela com a data Fato através da coluna COD_EMPRESA.

Durante a execução, após ter a tabela fato em memória, da leitura múltipla, o QlikView, faz a leitura do primeiro arquivo encontrado que é “NF_abr13.xls” (Por quê esse? Ordem Alfabética!) e realiza o LEFT JOIN com a tabela fato, depois disso o QlikView faz a leitura do próximo arquivo “NF_fev12.xls” e realiza o LEFT JOIN com a tabela fato. OPA !!!! neste momento a tabela Fato já possui as colunas do arquivo de Empresas: COD_EMPRESA e DES_EMPRESA, e as colunas do arquivo de Nota Fiscal: NF_NUMERO, NF_DT_PAGAM, NF_VL_PAGAM e COD_EMPRESA, ou seja, ao tentar fazer o LEFT JOIN do segundo arquivo “NF_fev12.xls”, o QlikView não vai encontrar (match) a combinação de registros das colunas (NF_NUMERO, NF_DT_PAGAM, NF_VL_PAGAM e COD_EMPRESA).

Não entendi!

No primeiro arquivo, a chave de ligação do LEFT JOIN é apenas a coluna COD_EMPRESA (essa é a chave correta), porém a partir do segundo arquivo, a chave de ligação passa a ser: COD_EMPESA, NF_NUMERO, NF_DT_PAGAM, e NF_VL_PAGAM porque essas colunas já foram carregadas no primeiro arquivo carregado.

Logicamente, essa nova chave não fará mais nenhuma ligação por não termos essa combinação de registros.

Desenhando (esse momento foi muito difícil pra mim rsrs!!!)

07

08

O que fazer?

Bom, se o comportamento do QlikView é esse, então nestes casos é sempre importante termos um passo antes para carregar todos esses arquivos e guardar em uma tabela temporária, para depois fazer o JOIN. Dessa forma:

09

E o concatenate?

Para concatenate não tem problema, pois o processo é de forçar a concatenação, independente de quais colunas estejam nas tabelas.

Conclusão

É muito importante conhecermos a fundo os conceitos e comportamentos do QlikView. Neste caso, nossa cabeça pensa que o QlikView vai carregar todos os arquivos (concatenando-os) para depois fazer o JOIN, porém aprendemos que o QlikView não se comporta dessa forma.

Para quem deseja, segue a aplicação de exemplo para download.

Até a próxima semana pessoal!

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!

Cálculo de Tempo Útil de Atendimento (SLA)

Fala pessoal! Tudo bom?

Em algum momento já se depararam com a possibilidade de cálculo do tempo de atendimento (SLA)? Considerando apenas o tempo útil da empresa?

Parece simples, mas não é!

Esse cálculo envolve muitas condicionais e tratativas. Confesso que a primeira vez que fui desenvolver, fiquei um bom tempo (MUIIITO TEMPO) pensando em todas as possibilidades do cálculo!

Vou citar algumas:

1 – Abre dentro do horário e fecha no mesmo dia dentro do horário
2 – Abre dentro do horário e fecha no mesmo dia fora do horário
3 – Abre fora do horário e fecha no mesmo dia dentro do horário
4 – Abre fora do horário e fecha no mesmo dia fora do horário
5 – Abre dentro do horário e fecha em outro dia dentro do horário
6 – Abre dentro do horário e fecha em outro dia fora do horário
7 – Abre fora do horário e fecha em outro dia dentro do horário
8 – Abre fora do horário e fecha em outro dia fora do horário
9 – Abre dentro do horário e fecha em outro dia dentro do horário
10 – Abre dentro do horário e fecha em outro dia fora do horário
11 – Abre fora do horário e fecha em outro dia dentro do horário
12 – Abre fora do horário e fecha em outro dia fora do horário
13 – Abre antes do final de semana dentro do horário e fecha no final de semana
14 – Abre antes do final de semana dentro do horário e fecha no próximo dia útil dentro do horário
15 – Abre antes do final de semana fora do horário e fecha no final de semana
16 – Abre antes do final de semana fora do horário e fecha no próximo dia útil fora do horário
17 – Abre antes do feriado dentro do horário e fecha no feriado
18 – Abre antes do feriado dentro do horário e no próximo dia útil após o feriado antes do horário
19 – Abre antes do feriado dentro do horário e no próximo dia útil após o feriado dentro do horário
20 – Abre antes do feriado fora do horário e no próximo dia útil após o feriado dentro do horário

E acredite, existem mais condicionais.

Vamos ao que interessa, a prática!

 

Funções

Para este exemplo, utilizaremos as seguintes funções:

  • FRAC
  • FLOOR
  • MakeTime
  • NetWorkDays
  • LastWorkDate
  • FABS
  • Interval

 

Manual!! Me ajude…

 

FRAC

Utilização: frac(x)

Descrição: Retorna a parte fracionária de um número.

Exemplos:
frac( 11,43 ) retorna 0,43

 

FLOOR

Utilização: floor(x [ , base [ , offset ]])

Descrição: Arredondamento de x para baixo até o múltiplo mais próximo de base com um deslocamento de offset. O
resultado é um número.

Exemplos:

floor( 2,4 ) retorna 2

floor(11,43) retorna 11

 

MakeTime

Utilização: MakeTime( hh [, mm [, ss [.fff ]]] )

Descrição: Retorna uma hora calculada a partir da hora hh, do minuto mm, do segundo ss com uma fração fff até um
valor em milissegundos.

Se nenhum minuto for indicado, 00 será assumido.
Se nenhum segundo for indicado, 00 será assumido.
Se nenhuma fração de segundo for indicada, 000 será assumido.
Exemplos:
maketime( 22 ) retorna 22:00:00
maketime( 22, 17 ) retorna 22:17:00
maketime( 22, 17, 52 ) retorna 22:17:52

 

NetWorkDays

Utilização: networkdays (data_inicial, data_final {, feriado})

Descrição: Retorna o número de dias úteis (segunda-sexta) entre e incluindo a data_inicial e a data_final,
levando em conta quaisquer feriados opcionais listados. Todos os parâmetros devem ser datas ou
datas/horas válidas.

Exemplos:
networkdays (‘2007-02-19’, ‘2007-03-01’) retorna 9
networkdays (‘2006-12-18’, ‘2006-12-31’, ‘2006-12-25’, ‘2006-12-26’) retorna 8

 

LastWorkDate

Utilização: lastworkdate(data_inicial, nº_de_dias_úteis {, feriado})

Descrição: Retorna a data final mais recente para obter o número_de_dias_úteis (segunda-sexta) se o início for
na data_inicial, considerando-se os feriados listados opcionalmente. Data_inicial e feriado devem
ser datas ou datas/horas válidas.

Exemplos:
lastworkdate (‘2007-02-19’, 9) retorna ‘2007-03-01’
lastworkdate (‘2006-12-18’, 8, ‘2006-12-25’, ‘2006-12-26’) retorna ‘2006-12-29’

 

FABS

Utilização: fabs(x)

Descrição: É o valor absoluto de x. O resultado é um número positivo.

Exemplos:
fabs( 2,4 ) retorna 2,4
fabs( -3.8 ) retorna 3.8

 

Interval

Utilização: interval( expressão [ , código de formato ])

Descrição: A função interval formata a expressão como um intervalo de tempo, de acordo com o caracter fornecido
como código de formato. Se o código de formato for omitido, será utilizado o formato de hora definido no
sistema operacional. Os intervalos podem ser formatados como hora, dia ou como uma combinação de dias,
horas, minutos, segundos e frações de segundos.

Exemplos:
Os exemplos abaixo supõem as seguintes configurações do sistema operacional:
Formato de data abreviada: YY-MM-DD
Formato de hora: hh:mm:ss
Separador de número decimal: .
interval( A ) em que A=0.375 retorna:
Caracter 09:00:00
Número 0.375
interval( A ) em que A=1.375 retorna:
Caracter 33:00:00
Número 1.375

 

Vamos ao código

Nosso exemplo vai calcular o tempo útil de SLA de uma empresa que possui como horário de trabalho das 08:00 até as 18:00 de Segunda à Sexta. Nessa empresa a hora do almoço é contabilizada!

Calcularemos o tempo de fechamento de chamados, ou seja, nossa tabela possui uma data de abertura e data de encerramento.

 

Vamos lá….

 

Em primeiro lugar crio uma variável chamada Feriado e cadastro os possíveis feriados, mas lembre-se de cadastrar esses feriados com os valores entre apóstrofes e delimitados por virgula “,”.

Segue exemplo:

SET Feriados = ’09/06/2015′,’10/06/2015′;

 

Em segundo, devo tratar a data de abertura e data de encerramento.

Para a coluna de data de abertura:

  • Se a abertura é antes das 08 horas, então passa a ser as 08 horas do mesmo dia
  • Se a abertura é após as 18 horas, então passa a ser as 08 horas do próximo dia útil (considerando feriados e finais de semana).

Para a coluna de data de encerramento:

  • Se o encerramento é antes das 08 horas, então passa a ser 18 horas do dia útil anterior
  • Se o encerramento é depois das 18 horas, então passa a ser 18 horas do mesmo dia

 

Em terceiro, faço os cálculos finais, com as condições:

  • Se a data de encerramento é igual ao dia de abertura, então faço uma coluna menos a outra e assim temos o tempo útil
  • Se a data de encerramento é diferente ao dia de abertura, então faço preciso fazer o seguinte cálculo:
    • A – uma coluna menos a outra para contabilizar a quantidade de horas no intervalo
    • B – Verifico a quantidade de dias que existe dentro desse intervalo e multiplico por 14 (14 horas não úteis dentro de um dia de 10 horas úteis). Lembre-se que das 08:00 até as 18:00 temos 10 horas, logo se o dia possui 24 horas, então neste dia temos 14 horas não úteis.
    • C – Verifico a quantidade de dias não uteis dentro do intervalo e multiplico por 24 (Esses dias não úteis são os Sáb, Dom e Feriados)
    • A conta final fica: A – B – C

 

Ufa!!

Bem chatinho né? Isso porque não estamos desconsiderando a hora de almoço!! Mas isso fica para um próximo post!

 

Desta vez não irei colar o código, mas peço que façam o download da aplicação de exemplo. Opa, Só clicar aqui para fazer o download!

 

Espero que gostem!

Até a próxima semana!

Evento – Qlik Visualize Your World

Fala pessoal! Tudo bom?

Venho aqui divulgar um evento de QlikView da própria Qlik. É o “Qlik Visualize Your World” que acontecerá no dia 16 de Junho as 16h no Noah Gastronomia.

A página visualizar mais informações e se registrar no evento clique aqui.

Analisei o escopo do evento e acredito que este não seja um evento muito técnico e sim mais voltado para os usuários finais, porém teremos alguns assuntos bem interessantes, veja a lista:

Agenda

Estou bastante ansioso para a Palestra de Big Data! 😀

O mais legal é podermos nos conhecer e trocarmos ideia no evento. Apareçam por lá!