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

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!