Коллеги, приветствую!
Эта статья создана по мотивам мастер-класса который проходил на одном из обучений по нашему курсу Профессиональная разработка в Qlik Sense. Вместе с участниками тренинга мы разбирали трансформацию данных в скрипте загрузки и работу с неудобными файлами Excel которые часто встречаются как источники данных.
Архив со страшными файлами с которыми проходила работа можно скачать тут https://disk.yandex.ru/d/rILatarmu5tzVg
Почему Excel файлы и что же страшного? Excel файлы часто выступают средством обмена между информационной системой-источником данных и BI-средством (в нашем примере Qlik Sense). Например, работая на крупные организации разработчики и аналитики сталкиваются часто с тем, что получение доступа к информационной системе-источнику данных может занимать несколько месяцев. Что же делать в таких случаях? Проще всего использовать промежуточный слой на котором выгруженные из системы-источника отчеты Excel трансформируются в таблицы Qlik и затем сохраняются в .qvd файлы так, как если бы загрузка проходила из информационной системы.
Когда мы просим прислать выгрузку из информационной системы в виде Excel файла или, допустим, мы файл с планами, то мы ожидаем что-то на подобие такого:
Однако обычно приходит что-то вроде такого:
Одного взгляда на такие файлы обычно достаточно чтобы впасть в глубокую депрессию. Давайте разберем что с этим файлом не так:
- Несколько таблиц, данные из которых надо объединить
- Иерархическая структура
- Итоги по горизонтали и вертикали
- Информация представлена в виде сводных таблиц — что неудобно при написании выражений для мер
- Часть нужной нам информации в тексте заголовка формы — в наименовании отчета дата начала и дата окончания периода актуальности данных.
Работа может быть осложнена тем, что файл не один а их десять, двадцать или сто штук.
Первая мысль которая нам может прийти в голову, особенно если файлов немного — вручную переделать эти таблицы Excel. Но это плохая идея. С большой вероятностью файлы такого типа придут к нам снова и снова придется тратить свое время на неприятную трансформацию данных.
Однако хорошая новость в том, что такие файлы прекрасно обрабатываются в Qlik Sense с помощью скрипта загрузки. О том, каким образом мы обрабатываем такие файлы с подробным объяснением всех подводных камней я рассказываю в видеоуроке.
Результаты работы
Приложение, созданное в Qlik Sense Desktop, вы можете скачать по этой ссылке . Оно содержит следующий код скрипта:
SET ThousandSep=’ ‘;
SET DecimalSep=’,’;
SET MoneyThousandSep=’ ‘;
SET MoneyDecimalSep=’,’;
SET MoneyFormat=’# ##0,00 ₽;-# ##0,00 ₽’;
SET TimeFormat=’h:mm:ss’;
SET DateFormat=’DD.MM.YYYY’;
SET TimestampFormat=’DD.MM.YYYY h:mm:ss[.fff]’;
SET FirstWeekDay=0;
SET BrokenWeeks=1;
SET ReferenceDay=0;
SET FirstMonthOfYear=1;
SET CollationLocale=’ru-RU’;
SET CreateSearchIndexOnReload=1;
SET MonthNames=’янв.;февр.;мар.;апр.;мая;июн.;июл.;авг.;сент.;окт.;нояб.;дек.’;
SET LongMonthNames=’январь;февраль;март;апрель;май;июнь;июль;август;сентябрь;октябрь;ноябрь;декабрь’;
SET DayNames=’пн;вт;ср;чт;пт;сб;вс’;
SET LongDayNames=’понедельник;вторник;среда;четверг;пятница;суббота;воскресенье’;
SET NumericalAbbreviation=’3:k;6:M;9:G;12:T;15:P;18:E;21:Z;24:Y;-3:m;-6:μ;-9:n;-12:p;-15:f;-18:a;-21:z;-24:y’;for each fileName in FileList(‘lib://Страшные файлы/Отчет*.xlsx’)
Временная_Актуальность:
FIRST 1 LOAD
SubField(B, ‘ ‘, 9) as ДатаНачала,
SubField(B, ‘ ‘, 11) as ДатаОкончания
FROM [$(fileName)]
(ooxml, no labels, table is Лист1);JOIN(Временная_Актуальность)
FIRST 1 LOAD
C as ОрганизацияНаименование,
FileName() as ИмяФайл,
Replace(Replace( FileName(), ‘Отчет ‘,»),’.xlsx’,») as Регион
FROM [$(fileName)]
(ooxml, no labels, header is 1 lines, table is Лист1);Актуальность:
LOAD
ДатаНачала,
ДатаОкончания,
ОрганизацияНаименование,
ИмяФайл,
Регион,
‘$(fileName)’ as ИмяФайлаПолное
RESIDENT Временная_Актуальность;DROP TABLE Временная_Актуальность;
Временная_Данные:
CROSSTABLE(МесяцГода, ПоказательЗначение, 4)
LOAD
RecNo() as НомерСтроки,
*
FROM [$(fileName)]
(ooxml, embedded labels, header is 3 lines, table is Лист1);Временная_ДанныеПолностью:
LOAD
НомерСтроки,
//Подразделение,
Категория,
Менеджер,
if(num#(МесяцГода) > MakeDate(2050,1,1), floor(num#(МесяцГода) / 10), num#(МесяцГода)) as МесяцГода,
if(num#(МесяцГода) > MakeDate(2050,1,1), ‘Расходы’, ‘Продажа’) as ПоказательТип,
ПоказательЗначение
RESIDENT Временная_Данные
WHERE
IsNum(num#(МесяцГода));DROP TABLE Временная_Данные;
INNER JOIN(Временная_ДанныеПолностью)
LOAD
RecNo() as НомерСтроки,
Подразделение,
FileName() as ИмяФайл
FROM [$(fileName)]
(ooxml, embedded labels, header is 3 lines, table is Лист1, filters(
Replace(1, bottom, StrCnd(null))
))
WHERE not IsNull(Категория) AND Категория <> »;Данные:
LOAD
*,
‘$(fileName)’ as ИмяФайлаПолное
RESIDENT Временная_ДанныеПолностью;DROP TABLE Временная_ДанныеПолностью;
next
INNER JOIN(Данные)
LOAD * RESIDENT Актуальность;DROP TABLE Актуальность;
