Normalizando Tabelas Cruzadas

De Qknow
Ir para: navegação, pesquisa

Introdução

Fontes de dados nem sempre estão normalizadas para um padrão mais usual e útil no QlikView, de maneira que as vezes algum tratamento se faz necessário. No exemplo de dados a seguir as colunas dos anos de 2007 a 2010 estão representando valores que deveriam estar distribuídos em duas colunas normalizadas (Ano e Valor). Como apresentada, a estrutura inviabiliza ou dificulta a geração de vários elementos gráficos e análises ao longo do tempo, motivo pelo qual podem ser invertidas para gerar uma nova tabela contendo uma forma mais usual de trabalho. Este post apresenta a instrução CROSS TABLE que atua para inverter valores que estão representados em colunas para uma única coluna de valores. O resultado será uma nova estrutura contendo os campos AnyField, Year e Value. Para testar o uso da instrução CROSS TABLE utilize o fragmento de carga abaixo.

DataSample:
LOAD * Inline [
AnyField,	             2007,     2008,	  2009,     2010
CustoOrcamento,             22000,    27000,	 31000,    34000
CustoOrcamento,             30000,    40000,	 38000,    37000
CustoOrcamento,             45000,    46000,	 48000,    50000
CustoOrcamento,             50000,    60000,	120000,   130000
CustoOrcamento,             65000,    75000,	105000,   160000
CustoReal,	            20000,    26000,	 33000,    32000
CustoReal,	            29000,    37000,	 32000,    31000
CustoReal,	            43000,    43000,	 45000,    46000
CustoReal,	            46000,    55000,	125000,   132000
CustoReal,	            73000,    80000,	102000,   145000
ReceitaOrcamento,	    13000,    38000,	 60000,    72000
ReceitaOrcamento,	    15000,    25000,	 29000,    65000
ReceitaOrcamento,	    15000,    30000,	 60000,    70000
ReceitaOrcamento,	    35000,    65000,	155000,   220000
ReceitaOrcamento,	    40000,    90000,	153000,   202000
ReceitaReal,	             6021,    53540,	 53446,    58826
ReceitaReal,	             6492,    24772,	 32779,    34355
ReceitaReal,	            25616,    58156,	 87656,    67939
ReceitaReal,	            25991,    75568,	205078,   157298
ReceitaReal,	            57521,    36073,	238352,   172170 ];


Instrução Cross Table

Realizar uma operação Cross Table significa transpor colunas separadas para uma única coluna contendo todos os valores enquanto o título das colunas são convertidos em uma outra coluna mantendo as relações entre os dados. A instrução possui a seguinte sintaxe:

crosstable (attribute field name, data field name [ , n ] ) ( loadstatement | selectstatement ) 

O parâmetro attribute field name contém o nome da coluna que será criada para armazenar os dados do atual cabeçalho utilizado que será convertido. Utilizando os dados da seção anterior como exemplo, este parâmetro receberá o valor Year ou Ano gerando uma nova coluna contendo os valores 2007, 2008, 2009 e 2010. O segundo parâmetro, data field name, conterá o nome da segunda coluna que será gerada a partir da rotação dos dados. Ou seja, os valores de cada ano serão inseridos debaixo do nome que for fornecido neste parâmetro. Por exemplo, pode ser que os valores sejam de Vendas ou qualquer nome que se deseja inserir. Por último, o parâmetro n indica o número de colunas que não serão alteradas pelo movimento da operação cruzada. O valor padrão é 1 e no exemplo acima represneta a coluna AnyField. Ou seja, esta é a única coluna que não possui movimento. Se mais colunas existirem será preciso fornecer o número correspondente aquelas que não serão giradas.

NewTable:
CrossTable(Year, Values)
LOAD AnyField, 
     [2007], 
     [2008], 
     [2009], 
     [2010]
Resident DataSample;

Esta instrução irá gerar uma nova tabela contendo o giro dos valores das colunas de 2007 a 2010 para duas novas colunas definidas como Year e Values. A tabela anterior não será mais necessária e pode ser excluída pela instrução DROP TABLE. Logo, o seguinte comando é executado:

DROP TABLE DataSample;
CrossTable1.PNG

Algumas características são importantes quando a instrução CROSSTABLE é utilizada:

  • A ordem das colunas altera o resultado. Por isso, as colunas inalteradas precisam ser especificadas como as primeiras do comando LOAD ou SELECT.
  • Se houver apenas uma coluna que não será girada no processo (no exemplo, AnyField), então apenas 2 parâmetros são necessários.
  • Quando 2 ou mais colunas não forem alteradas pela rotação, então o terceiro parâmetro precisa ser informado com o número de colunas inalteradas.
  • Como novas colunas podem ser inseridas no arquivo de origem (exemplo, o ano de 2011), é possível usar o asterísco (*) no lugar do nome das colunas.
  • O QlikView dispõe de um assistente de rotação, mas não atua sobre tabelas INLINE. Maiores detalhes sobre o assistente na próxima seção.
  • CROSSTABLE pode ser utilizado diretamente em uma tabela INLINE.
  • Atribua o nome das colunas que desejar obter como resultado, bastando informar os parâmetros attribute field name, data field name com o nome desejado.


Como mencionado, é possível utilizar o asterísco no lugar de nomear os campos individualmente para que a transformação possa incluir novos campos no futuro sem necessitar de manutenção no script. Portanto, o fragmento de código abaixo é uma alternativa ao exemplo mostrado:

NewTable:
CrossTable(Year, Values)
LOAD AnyField, * Resident DataSample;


Utilizando o Assistente

LoadFiles1.png
FileWizard1.png

O editor de scripts possui um assistente que auxilia na montagem de uma instrução CROSSTABLE, apesar do comando ser relativamente simples. Para usuários menos habituados com as instruções de script, o assistente é um excelente recurso. Para transformar um arquivo ou tabela de um banco de dados com o recurso de cruzar as colunas, basta abrir o editor de scripts pressionando as teclas CTRL + E ou selecionando Editar Script a partir do menu Arquivo do QlikView Desktop.

Para simular o passo a passo proposto por esta seção é possível utilizar um arquivo textual (.txt) disponível no link a seguir. Basicamente o arquivo possui a mesma estrutura do comando LOAD INLINE utilizado na introdução deste artigo. Com o Editor de Scripts aberto, o processo inicia-se com um clique no botão Arquivo de Tabelas. Após escolher o arquivo (no exemplo, qknow_cross_data.txt o assistente é exibido contendo uma amostra dos dados. O passo seguinte é configurar os aspectos referentes aos títulos de cada coluna, não necessariamente reconhecidos pelo assistente.

Ao informar que os Rótulos Estão Incluídos (Embedded Labels) o QlikView utilizará a primeira linha identificada no arquivo como título de cada coluna. Porém, é possível observar que o conteúdo das células contém uma vírgula, que pode ser removida configurando adequadamente a opção Delimitador (Delimiter) para Vírgula (Comma). Com estas configurações é possível avançar para a próxima etapa do assistente clicando no botão Próximo (Next).

Quando avançar para a próxima janela um único botão escrito Ativar Etapa de Transformação é exibido. Clique neste botão para visualizar as opções de transformação do assistente. Na janela de Transformação nenhuma alteração é necessária e o botão Próximo (Next) pode ser novamente acionado. Finalmente será possível realizar a operação de cruzamento das colunas com um clique no botão Tabela Cruzada (Crosstable).

CrossTable2.PNG

Com o acionamento do botão Crosstable abre-se uma janela do assistente para que sejam indicados 3 atributos, sendo o primeiro deles chamado de Campos Qualificados (Qualifier Fields) que correspondem as colunas que não devem sofrer movimentações. O número de colunas inalteradas neste exemplo será mantido em 1. Já o segundo parãmetro corresoponde ao nome da coluna que será criada para os valores dos anos (2007 a 2010). Neste campo entra-se com o texto Ano (ou Year). O terceiro e último parâmetro corresponde ao nome da coluna que herdará os valores após o cruzamento ter sido feito. Por exemplo, Valores (ou Values). A janela de configuração será semelhante a apresentada a seguir.

CrossTable3.PNG



Considerações e passos finais:

  • Com um clique no botão Ok da figura ao lado o assistente retorna a janela anterior.
  • É apresentado uma amostra do processo com os campos AnyField, Year e Values.
  • Com um clique no botão Próximo (Next) é possível observar o comando que será usado.
  • Para inserir a instrução dentro do script basta um clique no botão Finalizar (Finish).
  • O comando é inserido na posição onde estiver o cursor quando o assistente foi acionado.
  • O caminho para o arquivo depende das configurações do parâmetro Caminhos Relativos.
  • Quando Caminhos Relativos estiver desmarcado o assistente inclurá o diretório completo do arquivo.
  • Se Caminhos Relativos estiver marcado o assistente irá procurar o arquivo no mesmo local onde o QVW foi salvo.
  • A instrução criada pelo assistente será semelhante a apresentada a seguir.
  • Note que o caminho do arquivo irá variar de acordo com o local onde foi armazenado.
  • A configuração inicial de rótulos incluídos foi configurado no parâmetro embedded labels da cláusula FROM.
  • A configuração de separador como vírgula foi configurada no parâmetro delimiter is da cláusula FROM.


CrossTable(Year, Values)
LOAD AnyField, 
     [2007], 
     [2008], 
     [2009], 
     [2010]
FROM [Q:\SelfStudy\Cross Table\qknow_cross_data.txt]  (txt, codepage is 1252, embedded labels, delimiter is ',', msq);


Assuntos Relacionados


Download Disponível


Envelope01.jpg
Não encontrou o que procurava? Fale conosco!
Mail Us



Índice de Artigos | Página Principal