Funções Usuais no Tratamento de Dados

De Qknow
Ir para: navegação, pesquisa

Introdução

QlikView e Qlik Sense possuem um rico conjunto de funções de script para o tratamento de dados. Este artigo cobre algumas destas funções mais utilizadas durante os procedimentos de carga e transformação, demonstrando a sintaxe básica e o uso cotidiano destas operações que vão além dos exemplos contidos nos arquivos de ajuda do QlikView Desktop ou no portal de ajuda do Qlik Sense. No Qlik, as funções podem tratar caracteres, números, fluxo do script, condicionais, laços e afins. Embora estas categorias auxiliam na localização da função pretendida no help do produto, este artigo as apresenta a partir da perspectiva de uso mais frequente, independente destas categorias.

Mapeamento de Dados como ApplyMap()

A função ApplyMap é utilizada para uma troca do tipo DE > PARA. Ou seja, o valor inicial é substituído por um outro valor indicado em uma tabela previamente definida. Se a tabela do tipo DE -> PARA não existir no momento em que a função é utilizada nenhum dado será carregado na coluna e nenhuma mensagem de erro será informada. Para uso do ApplyMap() uma tabela de mapeamento precisa ser definida utilizando a instrução Mapping, tal como no exemplo abaixo. O comando Mapping pode ser utilizado em uma instrução LOAD ou a um SELECT direto de um tabela de banco de dados.

TrocaNomePais:
Mapping LOAD * INLINE [
Antes, Depois
Brazil, Brasil
Italy, Itália];

Ao ser invocada (chamada) a função ApplyMap() buscará na coluna Antes da tabela de mapeamento pelo valor informado na chamada, devolvendo o valor da coluna Depois. Se nenhum valor correspondente ao conteúdo da primeira coluna (Antes) for encontrado, então o valor original do campo será mantido se o terceiro parâmetro da função não for definido. A sintaxe básica da função ApplyMap() é:

ApplyMap(tabela_de_mapeamento, campo_ou_valor_a_ser_pesquisado, [resultado_alternativo]) As Campo

Considerando o fragmento de código acima é possível substituir os dados carregados em um determinado campo a partir da aplicação da função ApplyMap(), ajustando, corrigindo ou alterando aquilo que se deseja pela substituição em massa de todas as ocorrências do valor original no campo pelo valor substituído. No exemplo, os valores Brazil e Italy serão substituídos pelos respectivos Brasil e Itália quantas vezes foram encontrados no campo passado como parâmetro para a tabela de mapeamento.

LOAD ClienteID, 
     Cidade, 
     Estado, 
     CodigoPostal, 
     ApplyMap('TrocaNomePais', Pais, Cidade) As Pais,
     Fone, 
     Fax
FROM
[Clientes.xls] (biff, embedded labels, table is Clientes$);

Algumas considerações sobre o uso da tabela de mapeamento e da função ApplyMap():

  • A tabela de mapeamento deve ser definida antes do uso da função ApplyMap.
  • A instrução Mapping indica que a tabela de mapeamento é excluída no final do processamento do script, automaticamente.
  • Se o valor da coluna DE não for encontrado na tabela de mapeamento, persiste o valor original da coluna ou o valor definido no terceiro parâmetro.
  • Quando a tabela de mapeamento não é encontrada, nenhum erro é apresentado no script. Porém, nenhum dado é carregado ao campo.
  • O nome da tabela de mapeamento deve ser inserido entre aspas simples na chamada da função ApplyMap().
  • O terceiro parâmetro é opcional e indica o conteúdo que deverá ser gravado na coluna se o valor não for encontrado na tabela de mapeamento.
ApplyMap 01.PNG

Como dito, o terceiro parâmetro opcional presente na sintaxe da função ApplyMap() permite informar o valor a ser utilizado caso nenhuma correspondência tenha sido identificada. Por exemplo, é possível utilizar o texto Outros para o conteúdo do campo que não encontrar um registro correspondente na coluna DE. A sintaxe para essa funcionalidade é a mesma acrescida da string desejada, tal como:

ApplyMap('TrocaNomePais', Pais, 'Outros') As Pais

Use o fragmento de carga abaixo para testar o uso da função ApplyMap().

LOAD *, 
ApplyMap('TrocaNomePais', Country, City) As UpdatedLocation
INLINE [
CustomerId, Company, City, Country, Phone
17, Gluderstedt, Aachen, Germany, 0241-039123
65, X-Site, Albuquerque, USA, (505) 555-5939
55, SSS-Sport Shoes Store, Anchorage, USA, (907) 555-7584
94, Modehuset Abel, Århus, Denmark, 86 21 32 43
29, El Chandal del Barca, Barcelona, Spain, (93) 203 4560
46, Los Vatos Locos, Barquisimeto, Venezuela, (9) 331-6954
49, La Moda d'il Futuri, Bergamo, Italy, 035-640230
1, Eintrach GS, Berlin, Germany, 030-0074321
14, Das Alpen Shoe, Bern, Switzerland, 0452-076545
71, Sunny Ski Store, Boise, USA, (208) 555-8097
24, Sport & Fritid AB, Bräcke, Sweden, 0695-34 67 21
39, Noch Einmal GMBH, Brandenburg, Germany, 0555-09876
50, De la Vita, Bruxelles, Belgium, (02) 201 24 67
12, Los Pantalones Magicos, Buenos Aires, Argentina, (1) 135-5555
54, Los Espandrilos Fantasticos, Buenos Aires, Argentina, (1) 135-5333 ];

Neste segundo exemplo a instrução LOAD executa tanto a função ApplyMap() quanto a carga dos registros via INLINE. Apesar de não ser uma técnica muito intuitiva, a instrução carregará o conteúdo do INLINE e que será usado no ApplyMap() invocando a tabela de mapeamento definida no início do artigo. Como resultado, qualquer país que não seja encontrado na tabela de mapeamento será trocado pelo nome da cidade, gerando uma nova coluna denominada UpdatedLocation.

Substituição de Textos (MapSubstring)

Enquanto ApplyMap substitui todo o registro de um campo por outro valor (vide item anterior), MapSubstring troca parte do texto por outra definida em uma tabela de mapeamento que deve existir antes da execução da função. O exemplo a seguir demonstra como substituir os acentos existentes em uma coluna por valores sem acentos, equalizando a entrada de dados. Assuma, por exemplo, que os valores a seguir sejam carregados em um campo do QlikView ou Qlik Sense. Valores que deveriam estar escritos da mesma forma foram inseridos com acentos e sem acentos, gerando duplicidade no momento da apresentação em forma de lista ou mesmo em uso em elementos gráficos no papel de dimensões.

Especialidade
-------------
Ambulatório
Ambulatorio
Aval. Cárdio Pulmonar
Diagnóstico por Imagem
Ginásio
Ginasio
Hidroterapia
Laboratório de Movimento
LN Laboratório de Movimento
LN Urodinâmica
Neurofisiologia
Oficina Ortopédica
Patologia Cirúrgica
Patologia Clínica
Reabilitação Infantil
Urodinâmica

Uma tabela de mapeamento pode ser definida para substituir os caracteres dentro de um registro da coluna, trocando aqueles indesejados por outros equivalentes e necessários. O mapeamento pode ser definido pelas instruções Mapping Load ou Mappping Select, dependendo da fonte dos dados (arquivos e listas INLINE ou tabela de banco de dados). A seguinte tabela de mapeamento permite trocar caracteres acentuados por outros equivalentes.

TiraCaracterEspecial:
Mapping LOAD * INLINE [
 Char, Replace
 À,A
 Á,A
 Â,A
 Ã,A
 Ä,A
 Å,A
 Ç,C
 È,E
 É,E
 Ê,E
 Ë,E
 Ì,I
 Í,I
 Î,I
 Ï,I
 Ò,O
 Ó,O
 Ô,O
 Õ,O
 Ö,O
 Ù,U
 Ú,U
 Û,U
 Ü,U
 à,a
 á,a
 â,a
 ã,a
 ä,a
 å,a
 ç,c
 è,e
 é,e
 ê,e
 ë,e
 ì,i
 í,i
 î,i
 ï,i
 ò,o
 ó,o
 ô,o
 õ,o
 ö,o
 ù,u
 ú,u
 û,u
 ü,u
 .,/
];
LOAD MapSubString('TiraCaracterEspecial', Especialidade) Resident AreasHospitalares
ApplyMap 02.PNG

O uso da função MapSubString realiza a chamada para a tabela de mapeamento que por sua vez verifica a existência do caractere da coluna informada e realiza a busca no mapeamento, trocando quantas forem as ocorrências encontradas.

Considerações importantes sobre a função MapSubString:

  • A função não ignora o case das letras. Ou seja, maiúsculas e minúsculas são diferentes.
  • A inexistência da tabela de mapeamento gera uma mensagem de erro no script, diferente da função ApplyMap que simplesmente ignora.
  • A tabela de mapeamento pode ser definida por Mapping Load ou Mapping Select.
  • Mais de uma ocorrência de um caractere na mesma string será substituído.
  • O resultado pode ser observado na lista (figura) ao lado em QlikView. Os registros antes diferentes agora são iguais.
  • O nome dos campos na tabela de mapeamento não é relevante. Qualquer nome pode ser utilizado.

O uso da função MapSubstring() depende somente da necessidade do negócio e da criatividade do script. No exemplo a seguir pode ser necessário substituir siglas por textos completos, carregando informações mais claras ao usuário no momento de usar o painel. Assuma a carga de dados a seguir:

ReplaceSize:
Mapping Load * INLINE [
Before, After
M, Médio
G, Grande
P, Pequeno
B, Branco
V, Verde
A, Azul ];

[Products]:
LOAD *, 
MapSubString('ReplaceSize', ProductInfo) As NewProductInfo
INLINE [
ProductId, ProductInfo
1, M A
2, M B
3, G V
4, P A
5, M V B
6, G V ];
MapSubstring01.PNG

Considerações:

  • Para cada ocorrência das letras definidas no campo ProductInfo, a função MapSubstring() verifica a tabela de mapeamento.
  • Ao encontrar um valor correspondente na primeira coluna da tabela, o segundo valor é inserido no lugar da string (texto) original.
  • Se nenhum valor for encontrado, o texto permanece sem alterações.
  • A escrita deve considerar o formato, pois maiúsculas são diferentes de minúsculas na execução da função MapSubstring.
  • Se valores repetidos forem definidos na coluna "DE", o primeiro valor encontrado será utilizado para substituição e os demais, ignorados.
Nota:  As funções apresentada neste artigo foram testadas no QlikView 12 e Qlik Sense Junho 2017 (ou Sense 4.0).

Leitura de Registro Específico (Peek)

A função Peek permite ler uma linha específica da tabela atual informando a posição por meio de um parâmetro numérico. Caso nenhuma indicação de linha seja informada, Peek irá retornar o registro imediatamente anterior a linha atual. Logo, é possível criar uma nova coluna com valor acumulado a partir da função Peek. Utilizando a tabela a seguir é possível demonstrar a criação de uma coluna de valor acumulado.

Sintaxe: Peek(Coluna, Numero_da_Linha, '[Nome_da_Tabela_Opcional]')

Assumindo a seguinte carga, observe o uso da função para criação de uma nova coluna de valores acumulados.

TabelaPrimaria:
LOAD * INLINE [
Data, Valor
26/10/2000, 3
24/06/1999, 4
29/12/2000, 5
07/08/2000, 2
16/01/2001, 3
12/09/2000, 5
14/01/2001, 2
14/09/2000, 1
27/12/1998, 2
24/07/2000, 1
06/12/2000, 6
19/01/2001, 3
23/01/2001, 9];
TabelaAcumulada:
LOAD Data, Valor, NumSum(Valor, Peek('Acumulado')) As Acumulado Resident TabelaPrimaria;
Peek 1.PNG

Na instrução acima a função Peek é utilizada sem parâmetros para referenciar a linha anterior. No entanto, quando o script inicia a execução da instrução não há linha anterior ao primeiro registro. Desta forma, nenhum número é considerado para o campo Acumulado, enquanto o campo Valor é obtido no registro atual e gravado no campo Acumulado pela função NumSum.

No registro seguinte (segunda linha), a função Peek obtém o valor da coluna Acumulado (neste caso 3) que fora preenchido na linha anterior pelo primeiro registro do campo Valor. A operação NumSum soma o registro atual do campo valor ao registro anterior do campo Acumulado.

Assim a operação segue sempre somando o registro de Valor ao registro da linha anterior de Acumulado. Alternativamente, a função Peek poderá operar especificamente sobre uma linha, permitindo cálculos sobre uma posição específica da tabela.

Algumas considerações importantes sobre a função Peek:

  • Sem informar o parâmetro para o número da linha o valor do registro anteriormente carregado é utilizado.
  • Se a operação estiver na primeira linha, o valor zero é retornado.
  • A posição da primeira linha é caracterizado pelo valor zero (0).
  • A função Previous equivale a operação de Peek sem informar posição da linha.
  • Quando referenciando a coluna de uma outra tabela o último registro carregado é recuperado pela função Peek.

Em alguns casos a função é utilizada para retornar o único valor das colunas de uma tabela, com no exemplo de criação do calendário de datas.

TMP_MaxMinData:
LOAD
	Max(Data) AS Max,
	Min(Data) AS Min
Resident ATENDIMENTOS;

LET vMaxOrderDate = Peek('Max');
LET vMinOrderDate = Peek('Min');

Datas em Diferentes Formatos (Alt)

O tratamento de datas pode envolver carregar os valores do campo com diferentes formatos incluindo DD/MM/AAAA, AAAA/MM/DD ou qualquer outra variação. É possível precaver-se do formato a ser encontrado em um campo utilizando a função Alt em conjunto com a função Date, verificando qual formato se enquadra nos dados fornecidos. A sintaxe de Alt é elementar, representando qual alternativa é verdadeira a partir dos valores informados como parâmetro, neste caso, a função Date. Observando o código a seguir é possível prevenir o script para carregar datas em três diferentes formatos.

Sintaxe: Alt(caso1, caso2, caso3, caso_n, se_não)
LOAD DataPedido, Frete, FuncionarioID,
    Alt(Date( left(DataPedido, 10) , 'DD/MM/YYYY' ),
	Date( left(DataPedido, 10) , 'MM/DD/YYYY' ),
	Date( left(DataPedido, 10) , 'MM/DD/YY' ),
	'Nenhuma data válida' ); 
SQL SELECT DataPedido, Frete, FuncionarioID FROM Pedidos; 

Cada vez que um registro é lido no campo DataPedido a função Alt avalia se o conteúdo é válido a partir da função Date. O primeiro valor válido encontrado será utilizado como valor do campo. No primeiro teste Date tentará importar os 10 primeiros caracteres do campo DataPedido no formato dia, mês, ano com 4 dígitos. Se o conteúdo do campo não puder ser lido neste formato, a função Alt executará o segundo parâmetro, preenchido com a função Date avaliando os 10 primeiros caracteres do campo no formato mês, dia e ano com 4 dígitos. Se novamente os dados não puderem ser lidos, a terceira ocorrência da função Date será acionada tentando importar o registro no formato mês, dia e ano com 2 dígitos. Se nenhuma das opções for possível, o último parâmetro de Alt preencherá o registro com o texto Nenhuma Data Válida.

Nota:  Preencher a data inválida com texto foi utilizado apenas para fins de explicação. Recomenda-se preencher com vazio.


Envelope01.jpg
Procurando Algo? Fale Conosco!

Voltar | Índice de Artigos | Principal