Crosstable
El prefijo de carga crosstable se utiliza para transponer datos estructurados de una "tabla cruzada" o "tabla pivotante". Los datos estructurados de esta manera se encuentran habitualmente cuando se trabaja con fuentes de hojas de cálculo. El resultado y el objetivo del prefijo de carga crosstable es transponer dichas estructuras a un equivalente de tabla normal orientada a columnas, ya que esta estructura generalmente es más adecuada para el análisis en Qlik Sense.
Sintaxis:
crosstable (attribute field name, data field name [ , n ] ) ( loadstatement | selectstatement )
Argumento | Descripción |
---|---|
attribute field name | El nombre del campo de salida deseado que describe la dimensión orientada horizontalmente que se transpondrá (la fila de encabezado). |
data field name |
El nombre del campo de salida deseado que describe los datos orientados horizontalmente de la dimensión que se transpondrá (la matriz de valores de datos debajo de la fila del encabezado). |
n |
El número de campos cualificadores, o dimensiones no modificadas, que preceden a la tabla que se va a transformar a una forma genérica. El valor predeterminado es 1. |
Esta función de script se relaciona con las siguientes funciones:
Función | Interacción |
---|---|
Generic | Un prefijo de carga de transformación que toma un conjunto de datos estructurados entidad-atributo-valor y lo transforma en una estructura de tabla relacional normal, separando cada atributo encontrado en un nuevo campo o columna de datos. |
Ejemplo 1: transformar datos de ventas pivotados (simple)
Vista general
Abra el Editor de carga de datos y agregue el primer script de carga a continuación, en una nueva pestaña.
El primer script de carga contiene un conjunto de datos al que se aplicará el prefijo de script crosstable más adelante, con la sección de aplicación crosstable descomentada. Esto significa que se utilizó la sintaxis de comentarios para deshabilitar esta sección en el script de carga.
El segundo script de carga es el mismo que el primero, pero con la aplicación de crosstable no comentado (habilitado al eliminar la sintaxis de comentarios). Los scripts se muestran de esta manera para resaltar el valor de esta función de script en la transformación de datos.
Primer script de carga (función no 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 más información sobre el uso de cargas inline, vea Cargas inline .
Resultados
Cargue los datos y abra una hoja. Cree una nueva tabla y agregue estos campos como dimensiones:
-
Product
-
Jan 2021
-
Feb 2021
-
Mar 2021
-
Apr 2021
-
May 2021
-
Jun 2021
Producto | Ene 2021 | Feb 2021 | Mar 2021 | Abr 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 |
Este script permite la creación de una tabla cruzada con una columna para cada mes y una fila por producto. En su formato actual, estos datos no son fáciles de analizar. Sería mucho mejor tener todos los números en un campo y todos los meses en otro, en una tabla de tres columnas. La siguiente sección explica cómo hacer esta transformación en la tabla cruzada.
Segundo script de carga (función aplicada)
Descomente el script eliminando el //. El script de carga deberá presentar el siguiente aspecto:
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 más información sobre el uso de cargas inline, vea Cargas inline .
Resultados
Cargue los datos y abra una hoja. Cree una nueva tabla y agregue estos campos como dimensiones:
-
Product
-
Month
-
Sales
Producto | Month | Ventas |
---|---|---|
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 |
Una vez que se ha aplicado el prefijo de script, la tabla cruzada se transforma en una tabla simple con una columna para Month y otra para Sales. Esto mejora la legibilidad de los datos.
Ejemplo 2: transformar datos de objetivos de ventas pivotados en una estructura de tabla vertical (intermedio)
Vista general
Abra el editor de carga de datos y agregue el script de carga a continuación a una nueva pestaña.
El script de carga contiene:
-
Un conjunto de datos que se carga en una tabla denominada Targets (Objetivos).
-
El prefijo de carga crosstable, que transpone los nombres de las personas de ventas dinámicas en un campo propio, etiquetado Sales Person.
-
Los datos de objetivos de ventas asociados, que se estructuran en un campo llamado Target.
Script de carga
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
Cargue los datos y abra una hoja. Cree una nueva tabla y agregue estos campos como dimensiones:
-
Area
-
Sales Person
Añada esta medida:
=Sum(Target)
Área | Vendedor | =Sum(Target) |
---|---|---|
APAC | James | 1750 |
APAC | Lisa | 1500 |
APAC |
Sharon | 1850 |
EMEA | James | 950 |
EMEA | Lisa | 1350 |
EMEA | Sharon | 2050 |
N/D | James | 1200 |
N/D | Lisa | 1800 |
N/D | Sharon | 1350 |
Si desea replicar la visualización de datos como la tabla de entrada dinámica, puede crear una tabla pivotante equivalente en una hoja.
Haga lo siguiente:
- Copie y pegue la tabla que acaba de crear en la hoja.
- Arrastre el objeto gráfico de la Tabla pivotante a la parte superior de la copia de la tabla recién creada. Seleccione Convertir.
- Haga clic en Edición finalizada.
- Arrastre el campo Sales Person desde el estante de la columna vertical al estante de la columna horizontal.
La tabla siguiente muestra los datos en su forma de tabla inicial, como se indica en Qlik Sense:
Área | Vendedor | =Sum(Target) |
---|---|---|
Totales | - | 13800 |
APAC | James | 1750 |
APAC | Lisa | 1500 |
APAC |
Sharon | 1850 |
EMEA | James | 950 |
EMEA | Lisa | 1350 |
EMEA | Sharon | 2050 |
N/D | James | 1200 |
N/D | Lisa | 1800 |
N/D | Sharon | 1350 |
La tabla pivotante equivalente es similar a la siguiente, con la columna para el nombre de cada vendedor contenida dentro de la fila más grande para Sales Person:
Área | James | Lisa | Sharon |
---|---|---|---|
APAC | 1750 | 1500 | 1850 |
EMEA | 950 | 1350 | 2050 |
N/D | 1350 | 1350 | 1350 |
Ejemplo 3: transformar datos de objetivos y ventas dinámicas en una estructura de tabla vertical (avanzado)
Vista general
Abra el editor de carga de datos y agregue el script de carga a continuación a una nueva pestaña.
El script de carga contiene:
-
Un conjunto de datos que representa datos de ventas y objetivos, organizados por área y mes del año. Esto se carga en una tabla llamada SalesAndTargets.
-
El prefijo de carga crosstable. Esto se usa para despivotar la dimensión Month Year en un campo específico, así como para transponer la matriz de ventas y las cantidades objetivo en un campo específico llamado Amount.
-
Una conversión del campo de texto Month Year a una fecha adecuada, usando la función de conversión de texto a fecha date#. Este campo Month Year convertido a fecha se vuelve a unir a la tabla SalesAndTarget a través de un prefijo de carga Join.
Script de carga
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
Cargue los datos y abra una hoja. Cree una nueva tabla y agregue estos campos como dimensiones:
-
Area
-
Month Year
Cree la siguiente medida, con la etiqueta Actual:
=Sum({<Type={'Actual'}>} Amount)
Cree también la siguiente medida, con la etiqueta Target:
=Sum({<Type={'Target'}>} Amount)
Área | Mes Año | Actual | Objetivo |
---|---|---|---|
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 |
Si desea replicar la visualización de datos como la tabla de entrada dinámica, puede crear una tabla pivotante equivalente en una hoja.
Haga lo siguiente:
- Copie y pegue la tabla que acaba de crear en la hoja.
- Arrastre el objeto gráfico de la Tabla pivotante a la parte superior de la copia de la tabla recién creada. Seleccione Convertir.
- Haga clic en Edición finalizada.
- Arrastre el campo Month Year desde el estante de la columna vertical al estante de la columna horizontal.
- Arrastre el elemento Values desde el estante de la columna horizontal al estante de la columna vertical.
La tabla siguiente muestra los datos en su forma de tabla inicial, como se indica en Qlik Sense:
Área | Mes Año | Actual | Objetivo |
---|---|---|---|
Totales | - | 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 |
La tabla pivotante equivalente es similar a la siguiente, con la columna para el nombre de cada mes del año individual contenida dentro de la fila más grande de Month Year:
Area (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 - Actual | 435 | 434 | 397 | 404 | 458 | 447 | 413 | 458 | 385 | 421 | 448 | 397 |
APAC - Objetivo | 425 | 425 | 425 | 425 | 425 | 425 | 425 | 425 | 425 | 425 | 425 | 425 |
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 |
EMEA - Objetivo | 362.5 | 362.5 | 362.5 | 362.5 | 362.5 | 362.5 | 362.5 | 362.5 | 362.5 | 362.5 | 362.5 | 362.5 |
N/D - Actual | 378 | 415 | 363 | 356 | 403 | 343 | 401 | 365 | 393 | 340 | 360 | 405 |
N/D - Objetivo | 375 | 375 | 375 | 375 | 375 | 375 | 375 | 375 | 375 | 375 | 375 | 375 |