Вводное слово: Дата, Время, Timestamp

Пару слов о Timestamp – Это число с дробной частью, которое соответствует какой-то дате со временем. Т.е. это число можно представить в нужном формате Дата-Время.

Дата – это целая часть числа с дробной частью (про даты речь пойдет дальше). Время – это дробная часть числа (все, что после разделителя).

1 секунда ~ 0,0000115740740740740
23 часа 59 минут 59 секунд = 0,99998842592593

Шаг 1. Учимся понимать даты. Как хранятся даты в Qlik Sense

Для того, чтобы научиться работать с датами в Qlik Sense, необходимо понять исходный формат хранения дат.

Дата в Qlik – это целое число, которое может быть загружено в модель в формате даты, например, 20/03/2019 или 20.03.2019, или 20-03-2019, или 20+03+2019 (все зависит от Ваших потребностей и фантазии). Если данные с датами загружаются из файла, где формат даты уже задан, то этот формат будет загружен в модель. Если данные загружаются не из QVD файлов, а из сторонних файлов/систем/баз данных, то рекомендуется делать преобразование:
Формат даты источника данных => Число => Формат даты Qlik Sense.

Если избегать преобразования даты в число, а затем в дату, то можно наткнуться на следующие проблемы:

  • Загрузка в модель даты в исходном формате хранения (целое число);
  • Загрузка даты как текстового поля.

Что такое исходный формат хранения даты? Дата в Qlik Sense – это целое число. Проверить это можно с помощью формул в Qlik Sense приложении (например вбить в текстовом объекте листа формулу =Date(3000)).

Исходный формат даты в Qlik – целое число:

Date(-693959) = 01.01.0000
Date(-100000) = 16.03.1626
Date(-10000)  = 13.08.1872
Date(-5000)   = 22.04.1886
Date(-1000)   = 04.04.1897
Date(-2)      = 28.12.1899
Date(-1)      = 29.12.1899
Date(0)       = 30.12.1899
Date(1)       = 31.12.1899
Date(2)       = 01.01.1900
Date(43500)   = 04.02.2019

Шаг 2. Формат даты. Настройка формата даты по умолчанию

Настройка системной переменной “Формат даты по-умолчанию”

Формат даты по умолчанию в рамках 1 приложения настраивается с помощью переменной на вкладке MAIN в скрипте:

Настройка форматы даты по-умолчанию в рамках 1 приложения Qlik Sense
SET DateFormat='DD.MM.YYYY';

К этой настройке относитесь очень внимательно, т.к. функции дат, например Date(), без обозначения формата даты используют формат даты по-умолчанию.

Формат даты. Примеры

Рассмотрим примеры записи форматов даты:

Date(43500,'DD.MM.YYYY') = 04.02.2019
Date(43500,'DD.MM.YY')   = 04.02.19
Date(43500,'DDMMYYYY')   = 04022019
Date(43500,'DD/MM/YYYY') = 04/02/2019
Date(43500,'DD+MM+YYYY') = 04+02+2019
Date(43500,'DD|MM|YYYY') = 04|02|2019
Date(43500,'DD MM YYYY') = 04 02 2019

Date(43500,'DD.MMM.YYYY') = 04.февр..2019
Date(43500,'DD MMM YYYY') = 04 февр. 2019
Date(43500,'DD/MMM/YYYY') = 04/февр./2019
Date(43500,'D/MMM/YYYY')  = 4/февр./2019
Date(43500,'D MMMM YYYY') = 4 февраль 2019
Date(43500,'D MMMM YY')   = 4 февраль 19

Date(43500,'YYYY.MM.DD') = 2019.02.04
Date(43500,'YYYYMMDD')   = 20190204
Date(43500,'YYYY-MM')    = 2019-02
Date(43500,'YYYY-DD-MM') = 2019-04-02

Шаг 3. Топ 16 функций для Script (ETL – выгрузки, преобразования данных и загрузки в модель)

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

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

Обзор функций трансформации дат

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

Мой топ функций для работы с датами:

  1. Floor() – из числа с дробной частью (дата и время) оставляет только целую часть, что соответствует дате. Иногда требуется в скриптах, если нет уверенности, что поле в источнике содержит “чистую” дату, без составляющей времени. Или если из формата даты и время требуется получить дату.
  2. Date#() – читает/парсит строку в заданном формате и преобразует в понятный формат даты Qlik Sense (т.е. текстовая строка будет загружена как дата, а храниться будет как строка). Например, Date#(‘2019-01-21′,’YYYY-MM-DD’) будет загружено в Qlik Sense не как текстовая строка, а как дата 2019-01-21 (в скобках указано правило, по которому Qlik Sense интерпретирует строку в дату);
  3. Date() – задает нужный формат для поля типа “Дата” (т.е. значение хранится как число в заданном формате, а функция Date() изменяет формат, если это необходимо, либо присваивает формат по-умолчанию). Пример использования
    Date(Date#(‘2019-01-21′,’YYYY-MM-DD’),’DD/MM/YYYY’) = 21/01/2019
    или
    Date(Date#(‘2019-01-21′,’YYYY-MM-DD’)) = 21.01.2019 (если в приложении дата по-умолчанию задана в формате DD.MM.YYYY);
  4. MakeDate() – с помощью этой функции можно сделать дату из года, месяца и дня. Например:
    • MakeDate(2019) = 01.01.2019
    • MakeDate(2019,2) = 01.02.2019
    • MakeDate(2019,3,24) = 24.03.2019
  5. Today() – функция возвращает текущую дату. Функцию можно использовать как в скрипте, так в выражениях диаграммы. У функции есть параметр timer_mode, полный синтаксис функции Today(timer_mode). Time_mode может принимать 3 значения:
    • 0 (день последней завершенной загрузки данных). Today(0) Возвращает день последней завершенной загрузки данных.
    • 1 (день вызова функции). При использовании в выражении диаграммы будет возвращен день вызова функции. При использовании в скрипте загрузки будет возвращен день начала текущей загрузки данных.
    • 2 (день открытия документа). Возвращает день открытия документа.
    • По-умолчанию time_mode = 1, т.е. Today() = Today(1)
  6. Комбинации Left()/Right()/Mid() и Date#() & Date() – если дата представлена как текстовая строка, но помимо даты в строке содержится другая информация, то вычленить дату из строки можно следующим способом.
    1. Сначала получаем подстроку с помощью функций Left()/Right()/Mid(). Например:
      • Left(‘20190105 YhfIY17 0038473’,8) = 20190105
      • Right(‘YhfIY17 0038473 2019-01-05’,10) = 2019-01-05
      • Mid(‘YhfIY17 2019-01-07 0038473’,index(‘YhfIY17 2019-01-07 0038473′,’ ‘)+1,10) = 2019-01-07
    2. Затем строка преобразуется (считывается) в дату с помощью функции Date#() и устанавливается новый формат отображения даты с помощью функции Date()
      • Date#(Right(‘YhfIY17 0038473 2019-01-05′,10),’YYYY-MM-DD’) = 2019-01-05
      • Date(Date#(Right(‘YhfIY17 0038473 2019-01-05′,10),’YYYY-MM-DD’),’YYYY/MM/DD’) = 2019/01/05
  7. AddMonths() – с помощью этой функции можно добавить или вычесть заданное число месяцев к дате. Например,
    • AddMonths(MakeDate(2018,5,31),-3) = 28.02.2018
    • AddMonths(MakeDate(2018,5,31),-1) = 30.04.2018
    • AddMonths(MakeDate(2018,5,31),0) = 31.05.2018
    • AddMonths(MakeDate(2018,5,31),3) = 31.08.2018
    • AddMonths(MakeDate(2018,5,31),4) = 30.09.2018
  8. AddYears() – добавляет или вычитает заданное количество лет к дате. Например, AddYears(MakeDate(2018,5,31),1) = 31.05.2019
  9. Month() – с помощью этой функции можно получить название месяца (берется из системной переменной MonthNames, которая задается в скрипте).
    • Month(MakeDate(2018,5,31)) = май
    • Num(Month(MakeDate(2018,5,31))) = 5
  10. MonthName() – функция возвращает месяц и год.
    • MonthName(MakeDate(2018,5,31)) = май 2018
  11. MonthStart() – возвращает первый день месяца
    • MonthStart(MakeDate(2018,5,31)) = 01.05.2018
  12. MonthEnd() – возвращает последний день месяца
    • MonthEnd(MakeDate(2018,5,31)) = 31.05.2018
  13. Year() – возвращает год
    • Year(MakeDate(2018,5,31)) = 2018
  14. Week() – возвращает номер недели
    • Week(MakeDate(2018,5,31)) = 22
  15. WeekDay() – возвращает день недели из системной переменной DayNames (см. скрипт)
    • WeekDay(MakeDate(2018,5,31)) = чт
  16. Day() – возвращает день из даты
    • Day(MakeDate(2018,5,31)) = 31

Подробнее про Date# и Date с примером

Date#() используется для интерпретации данных. Например

Date#('2018_24_10','YYYY_DD_MM')

говорит о том, что первые четыре цифры – это год, цифры между двумя ‘_ – это дата, а последние две – месяц. Причем, функция Date# не меняет формат даты, т.е. дата просто будет переведена из текста в число по правилу ‘YYYY_DD_MM’

Date() используется, чтобы сказать, как показать данные. Например:

Date(Today(),'YYYY^DD^MM') возвращает 2019^08^05
Date(Today(),'MM/DD/YY') возвращает 05/08/19

Сочетание обеих функций:

Date(Date#('2018_24_10','YYYY_DD_MM'),'MM/DD/YYYY') возвращает 10/24/2018

Длинное название месяца

Для того, чтобы получить длинное название месяца, необходимо применить следующий код (длинное название месяца берется из системной переменной LongMonthNames):

Text(Date(MakeDate(2018,9,21),'MMMM YYYY'))

Это выражение вернет ‘сентябрь 2018’. Обязательно оборачивайте в Text, иначе будет ошибка. Объясняю почему:

Date(MakeDate(2018,9,21),’MMMM YYYY’) тоже вернет ‘сентябрь 2018′, но храниться будет как дата 21.09.2018. И если Вы сравните две даты ’22 сентября 2018′ и ’21 сентября 2018’, то в формате ‘MMMM YYYY’ они будут не равны.

if(
    Date(MakeDate(2018,9,21),'MMMM YYYY')=Date(MakeDate(2018,9,22),'MMMM YYYY'),
    1,
    0
)

Результат: 0, т.е. ложь.

if(
    Text(Date(MakeDate(2018,9,21),'MMMM YYYY'))=Text(Date(MakeDate(2018,9,22),'MMMM YYYY')),
    1,
    0
)

Результат: 1

Шаг 4. Использование функций в выражениях (визуализации)

todo

Шаг 5. Кейсы/примеры работы с датами в Qlik Sense

Кейс 1. Обработка дат с английскими названиями месяцев

Данные хранятся в датах англоязычного календаря. Нужно произвести преобразование в русский формат даты:

Необходимо дату 23 June 2018 преобразовать в 23 июня 2018

Для преобразования используем следующий скрипт (с меппингом):

[MAP Месяц в число]:
Mapping
LOAD * Inline [
	Месяц, Месяц номер
	january, 1
	february, 2
	march, 3
	april, 4
	june, 6
	july, 7
	august, 8
	september, 9
	october, 10
	november, 11
	december, 12
];

Данные:
LOAD 
     Дата                            As [Дата исходная], 
     SubField(Дата,' ',1)            As День,
     lower(SubField(Дата,' ',2))     As Месяц,
     ApplyMap('MAP Месяц в число',lower(SubField(Дата,' ',2)),'#Не определено#')                        As МесяцНомер,
     SubField(Дата,' ',3)            As Год,
     Date(
     	MakeDate(
            	SubField(Дата,' ',3),
                ApplyMap('MAP Месяц в число',lower(SubField(Дата,' ',2))),
                SubField(Дата,' ',1)
                )
          	,'DD.MM.YYYY'
          ) 			As Дата
FROM [lib://excel_date/TB01_20190404_054148.xlsx]
(ooxml, embedded labels, table is [Sheet1 (2)]);

Получаем вот такой результат:

Шаг 6. Best Practice – Рекомендации по работе с датами в QS/QlikSense/Qlik Sense/Клик Сенс

  1. В выражениях диаграмм (в том числе в Set Analysis) стремитесь использовать переменные с датами. Это позволит минимизировать издержки на поддержку отчетов в Qlik Sense.

Инструкции по работе с датами в QlikView и Qlik Sense


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

Ваш e-mail не будет опубликован. Обязательные поля помечены *