Crosstable
O prefixo de carregamento crosstable é usado para transpor dados estruturados de "tabela cruzada" ou "tabela dinâmica". Dados estruturados dessa forma são comumente encontrados ao trabalhar com fontes de planilhas. A saída e o objetivo do prefixo de carregamento crosstable é transpor essas estruturas para uma tabela regular equivalente orientada por colunas, já que essa estrutura geralmente é mais adequada para análises no Qlik Sense.
Sintaxe:
crosstable (attribute field name, data field name [ , n ] ) ( loadstatement | selectstatement )
Argumento | Descrição |
---|---|
attribute field name | O nome do campo de saída desejado que descreve a dimensão horizontalmente orientada a ser transposta (a linha do cabeçalho). |
data field name |
O nome do campo de saída desejado que descreve os dados horizontalmente orientados da dimensão a ser transposta (a matriz de valores de dados abaixo da linha do cabeçalho). |
n |
O número de campos de qualificador, ou dimensões inalteradas, que precedem a tabela a ser transformada em um formato genérico. O valor padrão é 1. |
Essa função de script está relacionada às seguintes funções:
Função | Interação |
---|---|
Generic | Um prefixo de carregamento de transformação que usa um conjunto de dados estruturados de entidade/atributo/valor e o transforma em uma estrutura de tabela relacional regular, separando cada atributo encontrado em um novo campo ou coluna de dados. |
Exemplo 1: Transformando dados de vendas pivotados (simples)
Visão geral
Abra o Editor de carregamento de dados e adicione o primeiro script de carregamento abaixo a uma nova guia.
O primeiro script de carregamento contém um conjunto de dados ao qual o prefixo do script crosstable será aplicado posteriormente, com a seção aplicando crosstable assinalado como comentário. Isso significa que a sintaxe de comentário foi usada para desativar essa seção no script de carregamento.
O segundo script de carregamento é igual ao primeiro, mas com a aplicação de crosstable sem barras de comentário (o que é possível pela remoção da sintaxe de comentário). Os scripts são mostrados dessa forma para destacar o valor dessa função de script na transformação dos dados.
Primeiro script de carregamento (função não aplicada)
tmpData:
//Crosstable (MonthText, Sales)
Load * inline [
Product, Jan 2021, Feb 2021, Mar 2021, Apr 2021, May 2021, Jun 2021
A, 100, 98, 103, 63, 108, 82
B, 284, 279, 297, 305, 294, 292
C, 50, 53, 50, 54, 49, 51];
//Final:
//Load Product,
//Date(Date#(MonthText,'MMM YYYY'),'MMM YYYY') as Month,
//Sales
//Resident tmpData;
//Drop Table tmpData;
Para obter mais informações sobre como usar carregamentos inline, consulte Carregamentos inline.
Resultados
Carregue os dados e abra uma pasta. Crie uma nova tabela e adicione esses campos como dimensões:
-
Product
-
Jan 2021
-
Feb 2021
-
Mar 2021
-
Apr 2021
-
May 2021
-
Jun 2021
Product | Jan 2021 | Fev 2021 | Mar 2021 | Abr 2021 | Maio 2021 | Jun 2021 |
---|---|---|---|---|---|---|
A | 100 | 98 | 103 | 63 | 108 | 82 |
B | 284 | 279 | 297 | 305 | 294 | 292 |
C | 50 | 53 | 50 | 54 | 49 | 51 |
Esse script permite a criação de uma tabela cruzada com uma coluna para cada mês e uma linha por produto. Em seu formato atual, esses dados não são fáceis de analisar. Seria muito melhor ter todos os números em um único campo e todos os meses em outro, em uma tabela de três colunas. A próxima seção explica como fazer essa transformação para a tabela cruzada.
Segundo script de carregamento (função aplicada)
Remova as barras de comentário do script removendo //. O script de carregamento deve ter a seguinte aparência:
tmpData:
Crosstable (MonthText, Sales)
Load * inline [
Product, Jan 2021, Feb 2021, Mar 2021, Apr 2021, May 2021, Jun 2021
A, 100, 98, 103, 63, 108, 82
B, 284, 279, 297, 305, 294, 292
C, 50, 53, 50, 54, 49, 51];
Final:
Load Product,
Date(Date#(MonthText,'MMM YYYY'),'MMM YYYY') as Month,
Sales
Resident tmpData;
Drop Table tmpData;
Para obter mais informações sobre como usar carregamentos inline, consulte Carregamentos inline.
Resultados
Carregue os dados e abra uma pasta. Crie uma nova tabela e adicione esses campos como dimensões:
-
Product
-
Month
-
Sales
Product | Month | Sales |
---|---|---|
A | Jan 2021 | 100 |
A | Feb 2021 | 98 |
A | Mar 2021 | 103 |
A | Apr 2021 | 63 |
A | May 2021 | 108 |
A | Jun 2021 | 82 |
B | Jan 2021 | 284 |
B | Feb 2021 | 279 |
B | Mar 2021 | 297 |
B | Apr 2021 | 305 |
B | May 2021 | 294 |
B | Jun 2021 | 292 |
C | Jan 2021 | 50 |
C | Feb 2021 | 53 |
C | Mar 2021 | 50 |
C | Apr 2021 | 54 |
C | May 2021 | 49 |
C | Jun 2021 | 51 |
Depois que o prefixo do script for aplicado, a tabela cruzada é transformada em uma tabela estática com uma coluna para Month e outra para Sales. Isso melhora a legibilidade dos dados.
Exemplo 2: Transformação de dados pivotados de metas de vendas em uma estrutura de tabela vertical (intermediária)
Visão geral
Abra o editor da carga de dados e adicione o script de carregamento abaixo em uma nova guia.
O script de carregamento contém:
-
Um conjunto de dados que é carregado em uma tabela denominada Targets.
-
O prefixo de carregamento crosstable, que transpõe os nomes dos vendedores pivotados para um campo próprio, rotulado Sales Person.
-
Os dados da meta de vendas associados, que são estruturados em um campo chamado Target.
Script de carregamento
SalesTargets:
CROSSTABLE([Sales Person],Target,1)
LOAD
*
INLINE [
Area, Lisa, James, Sharon
APAC, 1500, 1750, 1850
EMEA, 1350, 950, 2050
NA, 1800, 1200, 1350
];
Resultados
Carregue os dados e abra uma pasta. Crie uma nova tabela e adicione esses campos como dimensões:
-
Area
-
Sales Person
Adicione esta medida:
=Sum(Target)
Área | Representante de vendas | =Sum(Target) |
---|---|---|
APAC | James | 1750 |
APAC | Lisa | 1500 |
APAC |
Sharon | 1850 |
EMEA | James | 950 |
EMEA | Lisa | 1350 |
EMEA | Sharon | 2050 |
NA | James | 1200 |
NA | Lisa | 1800 |
NA | Sharon | 1350 |
Se quiser replicar a exibição de dados como a tabela de entrada pivotada, você poderá criar uma tabela dinâmica equivalente em uma pasta.
Faça o seguinte:
- Copie e cole a tabela que você acabou de criar na pasta.
- Arraste o objeto de gráfico de Tabela dinâmica para cima da cópia da tabela recém-criada. Selecione Converter.
- Clique em Edição concluída.
- Arraste o campo Sales Person da divisória de coluna vertical até a divisória de coluna horizontal.
A tabela a seguir mostra os dados em sua forma de tabela inicial, conforme exibido no Qlik Sense:
Área | Representante de vendas | =Sum(Target) |
---|---|---|
Totais | - | 13800 |
APAC | James | 1750 |
APAC | Lisa | 1500 |
APAC |
Sharon | 1850 |
EMEA | James | 950 |
EMEA | Lisa | 1350 |
EMEA | Sharon | 2050 |
NA | James | 1200 |
NA | Lisa | 1800 |
NA | Sharon | 1350 |
A tabela dinâmica equivalente é semelhante ao seguinte, com a coluna do nome de cada vendedor contida na linha maior para Sales Person:
Área | James | Lisa | Sharon |
---|---|---|---|
APAC | 1750 | 1500 | 1850 |
EMEA | 950 | 1350 | 2050 |
NA | 1350 | 1350 | 1350 |
Exemplo 3: Transformação de dados pivotados de vendas e metas em uma estrutura de tabela vertical (avançada)
Visão geral
Abra o editor da carga de dados e adicione o script de carregamento abaixo em uma nova guia.
O script de carregamento contém:
-
Um conjunto de dados que representa dados de vendas e metas, organizados por área e mês do ano. Isso é carregado em uma tabela chamada SalesAndTargets.
-
O prefixo de carregamento crosstable. Isso é usado para despivotar a dimensão Month Year em um campo dedicado, bem como para transpor a matriz de valores de vendas e metas para um campo dedicado denominado Amount.
-
Uma conversão do campo Month Year de texto em uma data adequada, usando a função de conversão de texto em data date#. Esse campo Month Year convertido em data é unido novamente à tabela SalesAndTarget por meio de um prefixo de carregamento Join.
Script de carregamento
SalesAndTargets:
CROSSTABLE(MonthYearAsText,Amount,2)
LOAD
*
INLINE [
Area Type Jan-22 Feb-22 Mar-22 Apr-22 May-22 Jun-22 Jul-22 Aug-22 Sep-22 Oct-22 Nov-22 Dec-22
APAC Target 425 425 425 425 425 425 425 425 425 425 425 425
APAC Actual 435 434 397 404 458 447 413 458 385 421 448 397
EMEA Target 362.5 362.5 362.5 362.5 362.5 362.5 362.5 362.5 362.5 362.5 362.5 362.5
EMEA Actual 363.5 359.5 337.5 361.5 341.5 337.5 379.5 352.5 327.5 337.5 360.5 334.5
NA Target 375 375 375 375 375 375 375 375 375 375 375 375
NA Actual 378 415 363 356 403 343 401 365 393 340 360 405
] (delimiter is '\t');
tmp:
LOAD DISTINCT MonthYearAsText,date#(MonthYearAsText,'MMM-YY') AS [Month Year]
RESIDENT SalesAndTargets;
JOIN (SalesAndTargets)
LOAD * RESIDENT tmp;
DROP TABLE tmp;
DROP FIELD MonthYearAsText;
Resultados
Carregue os dados e abra uma pasta. Crie uma nova tabela e adicione esses campos como dimensões:
-
Area
-
Month Year
Crie a seguinte medida, com o rótulo Actual:
=Sum({<Type={'Actual'}>} Amount)
Crie também essa medida, com o rótulo Target:
=Sum({<Type={'Target'}>} Amount)
Área | Mês Ano | Real | Destino |
---|---|---|---|
APAC | Jan-22 | 435 | 425 |
APAC | Feb-22 | 434 | 425 |
APAC |
Mar-22 | 397 | 425 |
APAC | Apr-22 | 404 | 425 |
APAC | May-22 | 458 | 425 |
APAC | Jun-22 | 447 | 425 |
APAC | Jul-22 | 413 | 425 |
APAC | Aug-22 | 458 | 425 |
APAC | Sep-22 | 385 | 425 |
APAC | Oct-22 | 421 | 425 |
APAC | Nov-22 | 448 | 425 |
APAC | Dec-22 | 397 | 425 |
EMEA | Jan-22 | 363.5 | 362.5 |
EMEA | Feb-22 | 359.5 | 362.5 |
Se quiser replicar a exibição de dados como a tabela de entrada pivotada, você poderá criar uma tabela dinâmica equivalente em uma pasta.
Faça o seguinte:
- Copie e cole a tabela que você acabou de criar na pasta.
- Arraste o objeto de gráfico de Tabela dinâmica para cima da cópia da tabela recém-criada. Selecione Converter.
- Clique em Edição concluída.
- Arraste o campo Month Year da divisória de coluna vertical até a divisória de coluna horizontal.
- Arraste o item Values da divisória de coluna horizontal até a divisória de coluna vertical.
A tabela a seguir mostra os dados em sua forma de tabela inicial, conforme exibido no Qlik Sense:
Área | Mês Ano | Real | Destino |
---|---|---|---|
Totais | - | 13812 | 13950 |
APAC | Jan-22 | 435 | 425 |
APAC | Feb-22 | 434 | 425 |
APAC |
Mar-22 | 397 | 425 |
APAC | Apr-22 | 404 | 425 |
APAC | May-22 | 458 | 425 |
APAC | Jun-22 | 447 | 425 |
APAC | Jul-22 | 413 | 425 |
APAC | Aug-22 | 458 | 425 |
APAC | Sep-22 | 385 | 425 |
APAC | Oct-22 | 421 | 425 |
APAC | Nov-22 | 448 | 425 |
APAC | Dec-22 | 397 | 425 |
EMEA | Jan-22 | 363.5 | 362.5 |
EMEA | Feb-22 | 359.5 | 362.5 |
A tabela dinâmica equivalente é semelhante à seguinte, com a coluna para cada mês individual do ano contida na linha maior para Month Year:
Área (valores) | Jan-22 | Feb-22 | Mar-22 | Apr-22 | May-22 | Jun-22 | Jul-22 | Aug-22 | Sep-22 | Oct-22 | Nov-22 | Dec-22 |
---|---|---|---|---|---|---|---|---|---|---|---|---|
APAC - Real | 435 | 434 | 397 | 404 | 458 | 447 | 413 | 458 | 385 | 421 | 448 | 397 |
APAC - Alvo | 425 | 425 | 425 | 425 | 425 | 425 | 425 | 425 | 425 | 425 | 425 | 425 |
EMEA - Real | 363.5 | 359.5 | 337.5 | 361.5 | 341.5 | 337.5 | 379.5 | 352.5 | 327.5 | 337.5 | 360.5 | 334.5 |
EMEA - Alvo | 362.5 | 362.5 | 362.5 | 362.5 | 362.5 | 362.5 | 362.5 | 362.5 | 362.5 | 362.5 | 362.5 | 362.5 |
NA - Real | 378 | 415 | 363 | 356 | 403 | 343 | 401 | 365 | 393 | 340 | 360 | 405 |
NA - Alvo | 375 | 375 | 375 | 375 | 375 | 375 | 375 | 375 | 375 | 375 | 375 | 375 |