Crosstable
Префикс загрузки crosstable используется для транспонирования структурированных данных «перекрестной таблицы» или «сводной таблицы». Данные, структурированные этим образом, обычно встречаются при работе с такими источниками, как электронные таблицы. Результатом и целью префикса загрузки crosstable является транспонирование таких структур в эквивалент обычной столбцовой таблицы, поскольку эта структура обычно лучше подходит для анализа в Qlik Sense.
Синтаксис:
crosstable (attribute field name, data field name [ , n ] ) ( loadstatement | selectstatement )
Аргумент | Описание |
---|---|
attribute field name | Желаемое имя выходного поля, описывающее горизонтально ориентированное измерение, которое необходимо преобразовать (строка заголовка). |
data field name |
Желаемое имя выходного поля, описывающее горизонтально ориентированные данные измерения, которое необходимо преобразовать (матрица значений данных под строкой заголовка). |
n |
Число полей-классификаторов, или неизмененных измерений, перед таблицей, которые следует преобразовать в общий формат. Значение по умолчанию — 1. |
Эта функция скрипта связана со следующими функциями:
Функция | Взаимодействие |
---|---|
Generic | Префикс загрузки преобразования, который принимает структурированный набор данных «объект-атрибут-значение» и преобразует его в обычную структуру реляционной таблицы, распределяя каждый встречающийся атрибут в новое поле или столбец данных. |
Пример 1. Преобразование сводных данных о продажах (простое)
Обзор
Откройте редактор загрузки данных и добавьте первый приведенный ниже скрипт загрузки на новую вкладку.
Первый скрипт загрузки содержит набор данных, к которому позднее будет применен префикс скрипта crosstable, при этом раздел, применяющий crosstable, закомментирован. Это означает, что для отключения этого раздела в скрипте загрузки использовался синтаксис комментария.
Второй скрипт загрузки такой же, как и первый, но с применением раскомментированного crosstable (для этого удаляется синтаксис комментария). Скрипты показаны таким образом, чтобы подчеркнуть пользу этой функции скрипта при преобразовании данных.
Первый скрипт загрузки (функция не применяется)
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;
Для получения дополнительной информации об использовании встроенных загрузок см. Встроенные загрузки.
Результаты
Загрузите данные и откройте лист. Создайте новую таблицу и добавьте эти поля как измерения:
-
Product
-
Jan 2021
-
Feb 2021
-
Mar 2021
-
Apr 2021
-
May 2021
-
Jun 2021
Продукт | Январь 2021 г. | Февраль 2021 г. | Март 2021 г. | Апрель 2021 г. | Май 2021 г. | Июнь 2021 г. |
---|---|---|---|---|---|---|
A | 100 | 98 | 103 | 63 | 108 | 82 |
B | 284 | 279 | 297 | 305 | 294 | 292 |
C | 50 | 53 | 50 | 54 | 49 | 51 |
Этот скрипт позволяет создать перекрестную таблицу с одним столбцом для каждого месяца и с одной строкой для каждого продукта. Текущий формат усложняет анализ данных. Будет намного лучше, если все числа будут в одном поле, а все месяцы в другом — в таблице с тремя столбцами. В следующем разделе объясняется, как выполнить это преобразование перекрестной таблицы.
Второй скрипт загрузки (функция применяется)
Раскомментируйте скрипт, удалив //. Скрипт загрузки будет выглядеть так:
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;
Для получения дополнительной информации об использовании встроенных загрузок см. Встроенные загрузки.
Результаты
Загрузите данные и откройте лист. Создайте новую таблицу и добавьте эти поля как измерения:
-
Product
-
Month
-
Sales
Продукт | Месяц | 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 |
После применения префикса скрипта перекрестная таблица трансформируется в прямую таблицу, в которой один столбец будет для Month, а другой для Sales. Это улучшает восприятие данных.
Пример 2. Преобразование сводных данных о целевых объемах продаж в вертикальную структуру таблицы (промежуточный вариант)
Обзор
Откройте редактор загрузки данных и добавьте приведенный ниже скрипт загрузки на новую вкладку.
Скрипт загрузки содержит следующее:
-
Набор данных, загруженный в таблицу с именем Targets.
-
Префикс загрузки crosstable, который переносит сводные данные об именах продавцов в отдельное поле с именем Sales Person.
-
Связанные данные о целевых объемах продаж, сгруппированные в поле с именем Target.
Скрипт загрузки
SalesTargets:
CROSSTABLE([Sales Person],Target,1)
LOAD
*
INLINE [
Area, Lisa, James, Sharon
APAC, 1500, 1750, 1850
EMEA, 1350, 950, 2050
NA, 1800, 1200, 1350
];
Результаты
Загрузите данные и откройте лист. Создайте новую таблицу и добавьте эти поля как измерения:
-
Area
-
Sales Person
Добавьте эту меру:
=Sum(Target)
Область | Менеджер по продажам | =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 |
Если требуется скопировать отображение данных в виде сводной входной таблицы, можно создать эквивалентную сводную таблицу на листе.
Выполните следующие действия.
- Скопируйте и вставьте только что созданную таблицу на лист.
- Перетащите объект диаграммы Сводная таблица над только что созданной копией таблицы. Выберите Преобразовать.
- Щелкните Изменение завершено.
- Перетащите поле Sales Person с полки вертикального столбца на полку горизонтального столбца.
В следующей таблице показаны данные в форме исходной таблицы, как они отображаются в Qlik Sense:
Область | Менеджер по продажам | =Sum(Target) |
---|---|---|
Итоги | - | 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 |
Эквивалентная сводная таблица выглядит примерно так, как показано ниже: столбцы для имени каждого продавца включены в более крупную строку для Sales Person:
Область | James | Lisa | Sharon |
---|---|---|---|
APAC | 1750 | 1500 | 1850 |
EMEA | 950 | 1350 | 2050 |
NA | 1350 | 1350 | 1350 |
Пример 3. Преобразование сводных данных о целевых объемах продаж в вертикальную структуру таблицы (расширенный вариант)
Обзор
Откройте редактор загрузки данных и добавьте приведенный ниже скрипт загрузки на новую вкладку.
Скрипт загрузки содержит следующее:
-
Набор данных, представляющий данные о продажах и целевых показателях, организованный по регионам и месяцам года. Набор данных загружается в таблицу под именем SalesAndTargets.
-
Префикс загрузки crosstable. Он используется для переноса измерения Month Year из сводной области в специальное поле, а также для переноса матрицы продаж и целевых сумм в специальное поле с именем Amount.
-
Преобразование поля Month Year из текста в правильную дату с использованием функции преобразования текста в дату date#. Это поле Month Year, преобразованное в дату, снова присоединяется к таблице SalesAndTarget с помощью префикса загрузки Join.
Скрипт загрузки
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;
Результаты
Загрузите данные и откройте лист. Создайте новую таблицу и добавьте эти поля как измерения:
-
Area
-
Month Year
Создайте следующую меру с меткой Actual:
=Sum({<Type={'Actual'}>} Amount)
Также создайте эту меру с меткой Target:
=Sum({<Type={'Target'}>} Amount)
Область | Месяц года | Actual | Цель |
---|---|---|---|
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 |
Если требуется скопировать отображение данных в виде сводной входной таблицы, можно создать эквивалентную сводную таблицу на листе.
Выполните следующие действия.
- Скопируйте и вставьте только что созданную таблицу на лист.
- Перетащите объект диаграммы Сводная таблица над только что созданной копией таблицы. Выберите Преобразовать.
- Щелкните Изменение завершено.
- Перетащите поле Month Year с полки вертикального столбца на полку горизонтального столбца.
- Перетащите поле Values с полки горизонтального столбца на полку вертикального столбца.
В следующей таблице показаны данные в форме исходной таблицы, как они отображаются в Qlik Sense:
Область | Month Year | Actual | Цель |
---|---|---|---|
Итоги | - | 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 |
Эквивалентная сводная таблица выглядит примерно так, как показано ниже: столбцы для каждого месяца включены в более крупную строку для Month Year:
Area (Values) | 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 - Target | 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 - 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 |
NA - Actual | 378 | 415 | 363 | 356 | 403 | 343 | 401 | 365 | 393 | 340 | 360 | 405 |
NA - Target | 375 | 375 | 375 | 375 | 375 | 375 | 375 | 375 | 375 | 375 | 375 | 375 |