3-2. Подготовка данных к созданию таблицы связей

Прежде чем мы начнем формировать таблицу связей, нам нужно привести таблицы модели данных в соответствие к следующим требованиям:

  1. Имена полей, которые будут использоваться для связи (поля дат в них не входят), одинаковые между всеми таблицами;
  2. Связи должны быть построены по единообразной структуре (если в одной таблице используются ИД сотрудников, в в другой их имена, то имена нужно заменить на ИД сотрудников);
  3. Имена полей, которые не будут использоваться для связи, должны иметь уникальное название на уровне всей модели;
  4. Поля дат, планируемые к использованию в едной временной оси, должны быть приведены ко дню (date, а не timestamp). Даты, которые относятся к агрегированным периодам должны быть приведены к первой дате этого периода (не январь 2021, а 01.01.2021. Не Q2 2022, а 01.04.2022)

Начнем же.

Управление именами полей в модели данных

Первый способ назначить имя полю — задать его в явном виде, через команду as.

Нам нужно переименовать поле «Contact ID» в «ID контакта» в загрузке таблицы контактов. И поле «ID сотрудника» в «ID ответственного» в загрузке справочника сотрудников.

Перезагрузим данные. Синтетические ключи никуда не делись, они стали даже еще страшнее. А таблица План по-прежнему летает в воздухе. Но, всему свое время.

Подмена значений поля через сопоставление

Чтобы таблица План могла быть задействована в модели, нам нужно решить вопрос с добавлением в нее ключевых полей. В ней есть поле Менеджер. Но оно содержит имя сотрудника. А у нас связь с сотрудниками должна идти через справочник Сотрудники. По идее, надо преобразовать имена сотрудников в Таблице план в идентификаторы сотрудников из таблицы Соттрудники.

Добавим в самом верху вкладки вот такую конструкцию. С помощью команды mapping перед оператором load, мы загружаем пары значений ФИО — ID отстветственного из справочника сотрудников в качестве таблицы сопоставления (маппинг).

В таких таблицах может быть не больше 2-х столбцов. Первый столбец — значения которые мы проверям, второй — на что меняем совпавшее заначение. Наименование полей не важен — поиск будет происходить именно по значениям полей.

Таблица сопоставлний не является частью модели данных и автоматически удаляется после завершения скрипта.

После создания маппинг можно применить в любой другой таблице. Поэтому обычно маппинги создаются в начале скрипта.

Применяется маппинг через функцию applymap(), которая имеет до 3-х аргументов:

  1. Название применяемого маппинга в одинарных кавычках;
  2. Значение, к которому применяется маппинг;
  3. Опционально — значение, которое проставляется в случае отсутствия совпадений. Если не указано, будет подставлено оригинальное название поля из второго аргумента. Также тут можно указывать функции, например применение следующего маппинга, и т.д.

Применяем наш маппинг в таблице планов к полю Менеджер, и не забываем назвать итоговое поле «ID ответственного». Загружаем данные. Теперь все таблицы связаны. Синтетический ключ, конечно же, на месте.

Массовая уникализация имен

С полями связей разобрались. Теперь, нужно исключить связи по не нужным полям, например таким как Название. В нашем примере мало полей, и переименовать их через as не составило бы труда. Но в реальной жизни модели будут наверняка сложнее. Больше полей, больше таблиц. Добавление полей в уже имеющиеся таблицы.

Все это делает ручную уникализацию довольно утомительной. А с выборочным переименованием полей вы рискуете однажды добавить в таблицу поле, которое совпадет с полем из другой таблицы, и ваша модел рассыпется как карточный домик.

В Qlik есть несколько способов автоматического переименования полей. Самая простая — это квалификация (функция qualify).

Эта функция вызывается между загрузками таблиц. После нее прописывается перечень имен полей, которые мы хотим квалифицировать. Квалификация подписывает к названию поля префикс в виде названия таблицы. Таким образом, достигается уникльность названия поля.

Поля для квалификации можно прописывать не только в явном виде, но и с использованием знаков подстановки символов, например *. Звездой можно заменить часть имени поля, или вообще все поля.

Qualify *; будет производить переименование всех полей в модели данных, что равноценно разрыву связей между всеми таблицами. Пропишите Qualify *; в начале вкладки Данные, и выполните скрипт.

Посмотрите в модель данных. Синтетических ключей нет, правда связей тоже нет. Все поля имеют префикс в виде названия таблицы, и как следствие все поля уникальны во всей модели.

Конечно, нас не устроит такой результат. Поэтому вместе с Qualify, мы будем использовать команду Unqualify. Эта команда исключает названные поля из списка квалификации.

После Qualify *; пропишите Unqualify «ID*»;

Это исключит из квалификации все поля, названия которых начинаются с ID. Конкретно этот способ подходит для нашего дата-сета, потому что мы познакомились об однообразии названий ключевых полей.

Действие квалификации продолжается до тех пор, пока не останавливается командой Unqualify *; Важно прописать ее в конце загрузки данных, т.к. мы не хотим, чтобы квалификация вмешивалась в построение таблицы связей, в которой мы будем оперировать полями из ранее загруженных таблиц.

Загрузите данные. Синтетический ключ вернулся, но теперь мы точно знаем, что у нас правильно размечены ключевые поля, и в них нет ничего лишнего.

Нормализация полей дат

Прежде чем мы перейдем к сборке таблицы связей, причешем поля дат для будущей единой временной оси. Как проверить, что с датами все ок? У этих полей должен стоять тег $date, который видно в просмотре модели данных.

Зайдем в просмотр модели, и поглядим на теги поля Сделки.Дата открытия.

В значениях поля мы видим даты. Но в тегах есть только $numeric и $timestamp. Это коварная ловушка источника данных, который подсовывает клику не тот формат данных, который по факту содержится в ячейке. На самом деле это не даты, а таймстампы, отформатированные как даты.

Т.е. 01.01.2021 12:45:51, которые показываются как 01.01.2021.

Вообще клик использует такой же формат чисел, как и Excel, и Google Sheets. Т.е. число 44 285,69 при форматировании в timestamp вернет нам такую метку времени: 30.03.2021 16:42:44.

Целая часть числа является счетчиком дней с 31.12.1899 года. Дробная часть числа — это время суток, где 0,5 — это 12 часов дня.

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

Этот результат можно получить через функцию floor() — она округляет до целого числа в меньшую сторону. Дополнительно, обернем это в функцию date(), которая форматирует входное число как дату — чтобы у нас на выходе были даты, а не значения типа 44165. Не забываем задать название этому полю.

Загружаем данные и смотрим в модель. У этого поля должен появиться тег $date. Тег $timestamp также никуда не исчезает, но это нормально. В конце концов, каждая дата может быть отформатирована как метка времени вроде 01.01.2021 00:00:00

Теперь посмотрим на поле Сделки.Дата закрытия. Вот сюрприз, оказывается даты в этом поле вовсе не даты, а текст! Это чревато для нас тем, что мы не сможем применить к этому значению функции по трансформации дат, например в месяцы и годы. Да и вообще с таким полем все будет работать не правильно.

Чтобы преобразовать текст в дату, нужно использовать функцию интерпретации, а именно date#(). Вообще у каждой функции формата, вроде num(), time(), timestamp() есть парная функция интерпретации с решеткой. Функции интерпретации позволяют преобразовать значения текста в число, на основе совпадения по маске.

Даты в поле Дата закрытия опеределнно соовтетствуют маске DD.MM.YYYY. Вдобавок, обернем эту конструкцию в функцию Date, чтобы получилось:

date(date#("Дата закрытия",'DD.MM.YYYY')) as "Дата закрытия",

Проверим наше поле. Чтож, оно стало числовым, но все еще без тега $date, хотя это точно даты.

Возможно вы заметили, что у нас там присутствуют интересные даты — 30.12.1899. Так выглядит число 0, представленное в виде даты. Дело в том, что это поле заполняется только при закрытии сделки. Соответственно у открытых сделок даты закрытия нет. В зависимости от источника, там могут содержаться или пустые значения, или что-нибудь тезническое типа 0.

От таких значений нужно избавляться, посколько они будут засорять временную ось, а при определенных условиях могут многократно увеличить ресурсы, потребляемые приложением. Избавиться от этих значений можно через замену их на null.

Это можно сделать через формулу с if, приравняв ненужные значения к функции null(). Либо прописав if так, чтобы дата расчитывалась только для нужных значений.

if("Дата закрытия"<>'30.12.1899',date(date#("Дата закрытия",'DD.MM.YYYY'))) as "Дата закрытия"

Результат: часть дат отмечена как прочерки (именно так отображаются пустые значения, которые null). Поле получило тег $date.

Напоследок, разберемся с датами в поле План.Месяц. Когда даты приходят в таком формате, остается лишь сказать «Спасибо, что хоть год подписали».

Преобразование дат из такого прям строкового текста может быть выоплнено большим кол-вом способов. С маппингом, и кучей вложенных if() и т.д. Но все-таки и тут можно применить с функцией интерпретации date#(), при условии, что все наименования месяцев написаны одинаково, и подчиняются одному формату (а не январь 2021 и янв. 21 в другой строке).

В секции Main посмотрите, какие значения прописаны в переменной LongMonthNames. Если это русские месяцы, которые начинаются с «Январь», то все ок. Если не русские, то русифицируйте их, и первый месяц пусть называется «Январь».

Теперь к полю Месяц в таблице План можно применить интерпретацию

date(date#(Месяц,'MMMM YYYY')) as Месяц

MMMM в маске месяца распознает полное назввание месяца. А MMM — сокращенное.

Результат:

Выводы

  1. Называйте поля для связи однообразно, с отличительным набором символов вроде ID. Это упростит вам квалификацию имен полей.
  2. Использование функционала квалификации имен полей гарантирует, что ваша модель не сломается при добавлении очередного поля в таблицу с данными.
  3. Обязательно контролируйте, что поля, которые будут формировать единую временную ось, имеют тег $date.
  4. Заменяйте значения недействиетльных дат на null(), для оптимизации и улучшения пользовательского опыта работы с вашим прилоежнием.
  5. Если в данных указана не дата а период, пересчитайте его в первую дату периода (первый день месяца, квартала и т.д.).
  6. Создавайте приложения, которые загружают данные и сохраняют таблицы в причесанном виде в QVD файлы (имена полей, форматы данных и т.д.). Загружайте данные из чистовых QVD файлов в аналитические приложения, не тратя время на повторную очистку данных.

Добавить комментарий

Ваш адрес email не будет опубликован.

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.