Melhores Práticas – ApplyMap

Fala pessoal, tudo bom?

 

Quero continuar comentando sobre melhores práticas para QlikView e neste capítulo vou falar um pouco sobre a função Applymap.

 

A função Applymap é nada mais, nada menos do que um De-Para, ou seja, substitui um valor por outro através de uma tabela de mapeamento.

Por que um de-para pode ser considerado uma melhor prática de desenvolvimento? Pois essa função, na maioria dos casos, pode ser utilizada para substituir a função JOIN com uma performance MUITO MAIOR. Lembrando que estamos falando no desempenho da execução de carga.
 
 
 

Explicação para utilização

  • Deve-se declarar uma tabela de mapeamento através da função Mapping;
  • A tabela de mapeamento deve conter apenas duas colunas;
  • A primeira coluna é sempre o De e segunda coluna sempre o Para;
  • A utilização deve ocorrer somente no método 1:1 (um para um);
  • Os nomes das colunas na tabela de mapeamento não são relevantes;
  • Por se tratar de uma tabela de mapeamento, ela existirá somente durante o carregamento (load);
  • Nenhuma associação (ligação) será feita nesta tabela;
  • Nenhum erro é gerado se você usar um nome de tabela de mapeamento que não existe

 
 
 

A função

A função ApplyMap possui três parâmetros, sendo:

  • Nome da tabela de mapeamento (deve ser usado entre apóstrofes) – Obrigatório
  • Nome do campo que sofrerá o De-para – Obrigatório
  • Retorno caso não seja encontrado nenhuma valor na tabela de mapeamento – Opcional – Caso não seja preenchido, será retornado o próprio valor do campo que não foi encontrado.

 
 
 

Exemplo simples de utilização

//Criando a tabela de mapeamento

MapaEstado:

Mapping LOAD

UF,

DESC_UF

From Estado.qvd (qvd);

 

//Utilizando a função

ApplyMap(‘MapaEstado’, UF, ‘Não Encontrei’) as DESCRICAO_ESTADO

 
 
 

Orientações

É muito importante utilizarmos o terceiro parâmetro da função com uma string que identifique que nenhum valor foi encontrado, pois dessa forma é possível identificar casos em que a regra utilizada não obteve valor.

Sempre utilize o terceiro parâmetro da função, para retornar algum valor nos casos em que não foi encontrado nenhum mapeamento.

 
 
 

Benefícios

O principal benefício da função é o desempenho na realização do De/Para. É sempre recomendado a utilização do ApplyMap ao invés de JOIN quando se trata da ligação entre duas tabelas grandes. Não acredita? Existem diversos cenários testados que comprovam, procure na comunidade :D!

Outro benefício é que, como dito anteriormente, o ApplyMap funciona apenas em casos de 1:1 (um para um), ou seja, um determinado registro possui apenas um valor a ser mapeado. Caso a função seja utilizada em casos N:1, então será retornado o primeiro registro (De N) do mapeamento para aquele caso e não será gerado nenhum produto cartesiano dos dados.

No exemplo abaixo, o vendedor Yuri possui dois apelidos, ‘Nicolett’ e ‘Yure’. Se fosse realizado um JOIN para interligar essas tabelas, os valores de vendas do vendedor Yuri seria duplicado devido as duas possibilidades de apelido, porém com ApplyMap, somente o primeiro valor encontrado no mapeamento seria retornado (‘Nicolett’) e os valores de vendas permaneceriam inalterados.

 
 

MapaApelido:
Mapping LOAD * INLINE [
NOME, APELIDO
Yuri, Nicolett
Yuri, Yure
Paula, Paulinha
];

 
Fato:
LOAD
ID,
NOME_VEND,
VALOR,
ApplyMap(‘MapaApelido’, NOME_VEND, ‘Achei não!’) as APELIDO
INLINE [
ID, NOME_VEND, VALOR
1, Yuri, 2
2, Yuri, 4
3, Paula, 5
];

 
 

Por último, e não menos importante, é possível se utilizar desse mapeamento para outra tratativa sem a necessidade de um pré-load ou resident, pois ao fazer o mapeamento o novo valor fica disponível para utilização naquele momento.

No exemplo abaixo é calculada o valor de comissão do vendedor em apenas 1 passo:

 

MapaComissao:

Mapping LOAD

VENDEDOR,

PERC_COMISSAO

FROM Comissao.qvd (qvd);

 

Fato:

LOAD

QTD_VENDA * ApplyMap(‘MapaComissao’, VEND, 0) as VALOR_COMISSAO

RESIDENT ….

 
 
 

Casos extremos

Mas Yuri, e se precisar recuperar 4 campos de outra tabela? Valeria a pena criar 4 mapeamentos ou fazer um único join?

Para identificar qual a melhor opção nessa caso eu precisaria de mais um detalhe: O volume de registros das duas tabelas é grande? Passa de 1 milhão de linhas? Vai variar muito daqui N tempo? Se o volume de dados é grande, então não tenho receio algum de te recomentar a utilização de 4 tabelas de mapeamentos e 4 applymaps, caso contrário utilize JOIN.

 
 
 

E mais alguns detalhes

Com o ApplyMap

O terceiro parâmetro da função ApplyMap é muito poderoso, pois você pode utilizar funções ou combinações de campos, por exemplo:

 

1) Retorne um conjunto de campos

ApplyMap(‘Mapeamento’, CAMPO, CAMPO2& ‘-‘ &CAMPO3)

 

2) Caso um mapeamento não funcione, tente outro.

ApplyMap(‘Mapeamento’, CAMPO, ApplyMap(‘MapeamentoDois’, CAMPO2))

/* Sinceramente esse caso é muito interessante, já presenciaram modelagens que a ligação entre duas tabelas pode ser realizada através de duas chaves diferentes? Dependendo da situação através de uma chave ou através de outra? Esse caso pode salvar sua vida rsrs */

 

3) Funções – Verifico condição

ApplyMap(‘Mapeamento’, CAMPO, if( CAMPO2 > CAMPO3, ‘É Maior’, ‘É Menor’))

 
 
 

Conclusão

É sempre bom se utilizarmos desse manual das melhores práticas e o ApplyMap é uma das principais funções para conseguirmos obter o melhor desempenho (de carga) dentro do script das nossas aplicações. Sempre analise seu atual ambiente e em caso de dúvidas faça o teste entre JOIN e ApplyMap.

 

 

 

Até a próxima semana!

 

Anúncios

Melhores Práticas – Hideprefix ou Hidesuffix

Fala pessoal, tudo bom?
 
Quero resgatar um pouco sobre o tópico de melhores práticas para o Qlikview e isso inclui a utilização da função Hideprefix.

Mas afinal o que é e para que server esta função?
Sua função serve essencialmente para transformar um ou diversos campos em campos de sistema, ou seja, se tornarão campos que não poderão ser utilizados pelo usuário e somente pelo desenvolvedor.
Essa é uma boa prática para evitar que nossos usuários possam obter informações incoerentes através de um campo que não possa ser utilizado no modelo.
 
 

Hideprefix

Todos os nomes de campos iniciados por um caractere de texto (ou pelo conjunto de caracteres) serão ocultos, da mesma forma que os campos de sistema.

Essa configuração funciona apenas para campos, não para variáveis.
 
 

Hidesuffix

Todos os nomes de campos terminados por um caractere de texto (ou pelo conjunto de caracteres) serão ocultos, da mesma forma que os campos de sistema.

Essa configuração funciona apenas para campos, não para variáveis.

 

Como posso utilizar essas funções?

Para ativar essas funções é necessário declará-las como variáveis. Estas variáveis são tratadas como variáveis de configurações do sistema.

 

 

Exemplos de utilização

 

Hideprefix

SET HidePrefix = ‘%’;

Neste exemplo campos iniciados com % serão tratados como campos do sistema.

 

Posso também declarar um prefixo camposto por mais de um caractere, por exemplo:

SET HidePrefix = ‘%C’;

Neste exemplo campos iniciados com %C serão tratados como campos do sistema.

 

Hidesuffix

SET Hidesuffix = ‘%’;

Neste exemplo campos terminados com % serão tratados como campos do sistema.

 

Da mesma forma que o Hideprefix, também é possível declarar um sufixo composto por mais de um caractere, por exemplo:

SET Hidesuffix = ‘ABA’;

Neste exemplo campos terminados com ABA serão tratados como campos do sistema.

 

Observação: Os campos de sistema podem ser visualizados pelo usuário desenvolvedor habilitando o checkbox “Mostrar campos do sistema” nas telas de Dimensões, expressões e lista de campos. Abaixo mostro onde encontrar essas opções em cada uma das telas:

 

Criação de objeto - Dimensões

Criação de objeto – Dimensões

Criação de objeto - Expressões

Criação de objeto – Expressões

Propriedades da Pasta - Campos

Propriedades da Pasta – Campos

 

Na prática

É sempre uma boa prática transformar os campos que são utilizados como chave de ligação entre as tabelas como um campo de sistema.

Em meu caso, sempre crio os campos chaves com o ‘%’ na frente e faço a omissão utilizando o Hideprefix.

 

Outro caso de utilização: Campos de dimensões ou expressões de relatório Ad-Hoc (Aquele tipo de relatório em que selecionamos as dimensões e expressões do gráfico através de dois campos com os valores. Colocarei um post de como faze-lo mais para frente) não devem estar disponibilizados na aplicação, devem ser tratados como campos de sistema.

 
 

Conclusão

Essa melhor prática não esta ligada a desempenho, e sim para ORGANIZAÇÃO. É importante termos o cuidado com quais campos devem ou não ser disponibilizados para o usuário, pois a tendência para o mundo Qlik (Sim, estou falando do Qlik Sense) é que os próprios usuários criem seus dashboards, relatórios e gráficos; e é uma de nossas responsabilidades disponibilizar somente o que realmente possui sentido para o usuário utilizar.

 

 

Nos vemos na próxima semana!

Abraços
 

 

 

Função para Criar Calendário (Utilizando Data existente no modelo)

Fala pessoal, tudo beleza?

No último post aprendemos a utilizar uma função para criação de calendário passando uma data início e data fim, hoje aprenderemos como verificar esse range através de um campo existente em nosso modelo.

Para fazer isso utilizaremos três funções: FieldValue, FieldValueCount e While.

  • FieldValue para retornar um valor do campo de data do modelo. Lembrando que sempre será retornado apenas um valor, para o nosso caso resolve, pois utilizaremos essa função junto com as funções MAX e MIN.
  • FieldValueCount utilizaremos para percorrer todos os possíveis valores do campo de data do modelo. Lembrando que a função retorna o número de valores distintos do campo escolhido.
  • While para criar todos os registro no intervalo entre a maior e menor data do modelo.

Vamos ao código:

SUB CriaCalendario(vCampoData,vNomeCalendario,vPrefixoCalendario,vCampoChave)

LET vNomeCalendario = If(Len(‘$(vNomeCalendario)’)=0,’Calendario’,’$(vNomeCalendario)’);
LET vPrefixoCalendario = If(Len(‘$(vPrefixoCalendario)’)=0,”,’$(vPrefixoCalendario)’);
LET vCampoChave = PurgeChar(vCampoChave,'”[]’);

[$(vNomeCalendario)]:
LOAD
Distinct
Data_ as [$(vCampoChave)],
Date(Data_, ‘$(DateFormat)’ ) as [$(vPrefixoCalendario)Data],
Year(Data_) as [$(vPrefixoCalendario)Ano],
Month(Data_) as [$(vPrefixoCalendario)Mes],
Day(Data_) as [$(vPrefixoCalendario)Dia],
Date(MonthStart(Data_), ‘MMM/YYYY’) as [$(vPrefixoCalendario)MesAno];
LOAD
Date(_DataInicio+(Iterno()-1),’$(DateFormat)’ ) as Data_
WHILE (_DataInicio+(Iterno()-1)<=_DataFim); //Regra para gerar todos os registro do intervalo
LOAD
Floor(Min(Fieldvalue(‘$(vCampoData)’,RecNo()))) as _DataInicio, //Função utilizada para buscar valor do campo data do modelo.
Floor(Max(Fieldvalue(‘$(vCampoData)’,RecNo()))) as _DataFim
AUTOGENERATE FieldValueCount(‘$(vCampoData)’); //Função necessária para passar por todos os registros possíveis do campo data do modelo.

ENDSUB;

 
 
OBSERVAÇÃO: É necessário utilizar o FieldValueCount no AutoGenerate, pois precisamos passar por todos os possíveis valores do campo da data. Caso você não utilize, pode ser que o Min e o Max sejam alterados.

 

Pronto, agora basta utilizar a função Call e passar os parâmetros para que o calendário seja criado, conforme exemplo abaixo:

Call CriaCalendario(‘DATA’,’Calendario’,”,’%ChaveData’);

Neste exemplo o calendário será criado com o intervalo entre o intervalo de datas do campo DATA (esse campo esta localizado na tabela Tabela_Fato do exemplo), o nome da tabela será “Calendario”, nenhum prefixo de campos será utilizado e o campo de chave com a minha modelagem é “%ChaveData”.

Para fazer o download da aplicação de exemplo, basta clicar aqui!

 

Por enquanto é isso pessoal!

Semana que vem abordaremos outro assunto!

Esclarecendo algumas dúvidas sobre o Blog

Fala pessoal, tudo bom?

Esse post não vai falar sobre qlikview, mas sim sobre o Blog.

Esse ano estou levando bem a sério o blog (uhul!) e muitas pessoas estão fazendo várias perguntas sobre os posts, sobre qual tipo de assunto vou abordar, etc.

  • Disponibilizarei um post por semana e esse post sempre entrará no ar entre sexta-feira e domingo, pois são os dias em que tenho mais tempo para me dedicar ao post.
  • Os assuntos dos posts são variados, podendo ser de layout, códigos, melhores práticas, dicas de acessos, servidores, extensões, macros, etc.
  • Pretendo colocar coisas básicas também, pois o público de QlikView é bem dividido entre novos e velhos na ferramenta.
  • Os arquivos de exemplo estarão armazenados em meu drive do google, caso alguém tenha problemas para fazer o download, é só me informar.

Meu contato para sugestões, críticas ou elogios: yuri.nicolett@gmail.com

A ideia principal do blog é ajudar a todos e me ajudar também 😀

Até o próximo post!

Função para Criar Calendário (Passando datas)

Fala pessoal!

 

Na maioria dos projetos em que iremos trabalhar é preciso criar um calendário para poder trabalhar com as datas de forma organizada (ainda não vou falar sobre um calendário principal, esse assunto vai ficar para um próximo post). Na maioria dos casos sempre opto por criar uma tabela de Calendário e trabalhar as datas nessa tabela, pois sempre tento organizar todas as datas necessárias para o projeto em uma única tabela (quando possível rsrs).

 

Hoje quero repassar para vocês uma função que utilizo para criar um calendário de uma forma com alta performance. Função? Sim, eu crio uma classe de função de script e depois utilizo uma linha passando os parâmetros necessários para criar o calendário.

 

Vamos entender um pouco mais sobre essas funções de script através do manual do QlikView:

O comando de controle sub e end sub define uma sub-rotina que pode ser chamada a partir de um comando call.

A sintaxe é:
sub nome [ ( listadeparâmetros )] comandos end sub

Onde:

  • name é o nome da sub-rotina.
  • listadeparâmetros é uma lista, separada por vírgulas, de nomes de variáveis para os parâmetros formais da subrotina. Eles podem ser usados como qualquer variável dentro da sub-rotina.
  • comandos são qualquer grupo de um ou mais comandos de script do QlikView.

 

Os argumentos são copiados na sub-rotina e, se os parâmetros reais correspondentes no comando call for o nome de uma variável, serão copiados novamente após a saída da sub-rotina.
Se uma sub-rotina tiver mais parâmetros formais que os parâmetros reais transmitidos por um comando call, os parâmetros extra serão inicializados como NULL e poderão ser utilizados como variáveis locais na subrotina.
Como o comando sub é um comando de controle e, por isso, termina com um ponto-e-vírgula ou com um fim de linha, cada uma de suas duas cláusulas (sub e end sub) não deve cruzar um limite de linha.

 

Agora vamos ao exemplo prático! No final do post existe um link para download de uma aplicação de exemplo 🙂

 
———————————————————————————————————–
 
 

Em primeiro lugar, cria um aba em seu script chamada Sub CriaCalendario nesta aba criaremos a função para criar o calendário. Segue agora o script:

 

/* Nome da função é CriaCalendario e esta recebe cinco parâmetros, são eles:

vDataInicio = Data de Início do seu relatório. O formato a ser passado pode seguir o modelo da formatação de data local, por exemplo DD/MM/YYYY.

vDataFim = Data de término do seu relatório. O formato a ser passado pode seguir o modelo da formatação de data local, por exemplo DD/MM/YYYY.

vNomeCalendario = Nome que receberá a tabela do seu calendário, caso nenhum seja digitado, então o nome Calendario será utilizado.

vPrefixoCalendario = Conjunto de caracteres que precederá todos os nomes de campos da tabela de calendário. Exemplo: “Calendario.”, então todos os campos possuirão o “Calendario.” antes do nome do campo da tabela. Caso nada seja utilizado, então nenhum prefixo será utilizado.

vCampoChave = Nome do campo que será chave entre a tabela de calendário e o seu modelo. Note que esse campo deve ser uma data completa, com dia, mês e ano.

*/

SUB CriaCalendario(vDataInicio,vDataFim,vNomeCalendario,vPrefixoCalendario,vCampoChave)

/* Algumas tratativas com os valores recebidos – Lembre-se que esses valores sempre serão tratados como variáveis. */
LET vNomeCalendario = If(Len(‘$(vNomeCalendario)’)=0,’Calendario’,’$(vNomeCalendario)’);
LET vPrefixoCalendario = If(Len(‘$(vPrefixoCalendario)’)=0,”,’$(vPrefixoCalendario)’);
LET vCampoChave = PurgeChar(vCampoChave,'”[]’);
/* Criação do calendário */
[“$(vNomeCalendario)”]:
LOAD
Distinct
Data_ as [$(vCampoChave)],
Date(Data_, ‘$(DateFormat)’ ) as [$(vPrefixoCalendario)Data],
Year(Data_) as [$(vPrefixoCalendario)Ano],
Month(Data_) as [$(vPrefixoCalendario)Mes],
Day(Data_) as [$(vPrefixoCalendario)Dia],
Date(MonthStart(Data_), ‘MMM/YYYY’) as [$(vPrefixoCalendario)MesAno];

LOAD
Date(‘$(vDataInicio)’+(Iterno()-1),’$(DateFormat)’ ) as Data_
AutoGenerate 1 While (Num(‘$(vDataInicio)’)+(Iterno()-1)<= Num(‘$(vDataFim)’));

/* Finalizando a função */
ENDSUB;

 

Pronto, agora basta utilizar a função Call e passar os parâmetros para que o calendário seja criado, conforme exemplo abaixo:

Call CriaCalendario(’01/01/2015′,’08/03/2015′,’Calendario’,”,’%ChaveData’);

 

Neste exemplo o calendário será criado com o intervalo entre 01/01/2015 e 08/03/2015, o nome da tabela será “Calendario”, nenhum prefixo de campos será utilizado e o campo de chave com a minha modelagem é “%ChaveData”.

 

IMPORTANTE: É necessário passar um valor para cada argumento criado na função, por esta razão passei um vazio para o argumento de número quatro que é o prefixo dos campos, caso contrário a função não executará corretamente.

 

Fiquem a vontade para editar e modelar da forma que preferirem para encaixar em seu modelo.

Para a próxima semana vou demonstrar como criar um calendário utilizando um intervalo de datas através de um campo de uma tabela.

 

Deixo aqui uma aplicação de exemplo para download.

 

Até a próxima semana!