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

Результирующая таблица
Продукт	Месяц	Продажи
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:

Исходная таблица результатов, как показано в 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:

Эквивалентная сводная таблица с горизонтальной ориентацией поля Sales Person
Область	James	Lisa	Sharon
APAC	1750	1500	1850
EMEA	950	1350	2050
NA	1350	1350	1350

Пример преобразования данных с помощью функции перекрестной таблицы. — Пример данных, отображаемых в виде таблицы, и эквивалентная сводная таблица с горизонтальной ориентацией поля Sales Person

Пример 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)

Таблица результатов (обрезанная)
Область	Месяц года	Факт	Цель
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:

Исходная таблица результатов (обрезанная), как показано в Qlik Sense
Область	Месяц года	Факт	Цель
Итоги	-	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:

Эквивалентная сводная таблица (обрезанная) с горизонтальной ориентацией поля Month Year
Область (значения)	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 ― факт	435	434	397	404	458	447	413	458	385	421	448	397
APAC ― цель	425	425	425	425	425	425	425	425	425	425	425	425
EMEA ―факт	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 ― цель	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 ― факт	378	415	363	356	403	343	401	365	393	340	360	405
NA ― цель	375	375	375	375	375	375	375	375	375	375	375	375

Пример данных, отображаемых в виде таблицы, и эквивалентная сводная таблица с горизонтальной ориентацией поля Month Year. — Пример данных, отображаемых в виде таблицы, и эквивалентная сводная таблица с горизонтальной ориентацией поля Month Year

Подробнее

Помогла ли вам эта страница?

Если вы обнаружили какую-либо проблему на этой странице и с ее содержанием — будь то опечатка, пропущенный шаг или техническая ошибка, сообщите нам об этом, чтобы мы смогли ее исправить!

Оставьте свой отзыв здесь