Содержание урока по Qlik Sense
Вводное слово: Дата, Время, 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 в скрипте:
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.
Понимание основ работы с данными и знание нескольких функций закроет на первое время все потребности при разработке скриптов загрузки данных.
Обзор функций трансформации дат
Функции нужны для преобразования дат, форматов, вычленения из даты года, месяца, дня, номера недели. К дате можно прибавить заданное количество дней, месяцев или лет. Для начала я кратко опишу каждую функцию и приведу примеры использования этих функций. Затем я рассмотрю несколько кейсов работы с датами в нестандартных случаях.
Мой топ функций для работы с датами:
- Floor() – из числа с дробной частью (дата и время) оставляет только целую часть, что соответствует дате. Иногда требуется в скриптах, если нет уверенности, что поле в источнике содержит “чистую” дату, без составляющей времени. Или если из формата даты и время требуется получить дату.
- Date#() – читает/парсит строку в заданном формате и преобразует в понятный формат даты Qlik Sense (т.е. текстовая строка будет загружена как дата, а храниться будет как строка). Например, Date#(‘2019-01-21′,’YYYY-MM-DD’) будет загружено в Qlik Sense не как текстовая строка, а как дата 2019-01-21 (в скобках указано правило, по которому Qlik Sense интерпретирует строку в дату);
- 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); - MakeDate() – с помощью этой функции можно сделать дату из года, месяца и дня. Например:
- MakeDate(2019) = 01.01.2019
- MakeDate(2019,2) = 01.02.2019
- MakeDate(2019,3,24) = 24.03.2019
- Today() – функция возвращает текущую дату. Функцию можно использовать как в скрипте, так в выражениях диаграммы. У функции есть параметр timer_mode, полный синтаксис функции Today(timer_mode). Time_mode может принимать 3 значения:
- 0 (день последней завершенной загрузки данных). Today(0) Возвращает день последней завершенной загрузки данных.
- 1 (день вызова функции). При использовании в выражении диаграммы будет возвращен день вызова функции. При использовании в скрипте загрузки будет возвращен день начала текущей загрузки данных.
- 2 (день открытия документа). Возвращает день открытия документа.
- По-умолчанию time_mode = 1, т.е. Today() = Today(1)
- Комбинации Left()/Right()/Mid() и Date#() & Date() – если дата представлена как текстовая строка, но помимо даты в строке содержится другая информация, то вычленить дату из строки можно следующим способом.
- Сначала получаем подстроку с помощью функций 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
- Затем строка преобразуется (считывается) в дату с помощью функции 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
- Сначала получаем подстроку с помощью функций Left()/Right()/Mid(). Например:
- 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
- AddYears() – добавляет или вычитает заданное количество лет к дате. Например, AddYears(MakeDate(2018,5,31),1) = 31.05.2019
- Month() – с помощью этой функции можно получить название месяца (берется из системной переменной MonthNames, которая задается в скрипте).
- Month(MakeDate(2018,5,31)) = май
- Num(Month(MakeDate(2018,5,31))) = 5
- MonthName() – функция возвращает месяц и год.
- MonthName(MakeDate(2018,5,31)) = май 2018
- MonthStart() – возвращает первый день месяца
- MonthStart(MakeDate(2018,5,31)) = 01.05.2018
- MonthEnd() – возвращает последний день месяца
- MonthEnd(MakeDate(2018,5,31)) = 31.05.2018
- Year() – возвращает год
- Year(MakeDate(2018,5,31)) = 2018
- Week() – возвращает номер недели
- Week(MakeDate(2018,5,31)) = 22
- WeekDay() – возвращает день недели из системной переменной DayNames (см. скрипт)
- WeekDay(MakeDate(2018,5,31)) = чт
- 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
Вывод “Месяц Год” и “Неделя Год” в числовом формате
Пример 1:
Num#(Date(MakeDate(2019,4,19),'MMYYYY')) //Вернет 042019
Пример 2:
Num#(Week(MakeDate(2019,4,19))&Year(MakeDate(2019,4,19))) //Вернет 162019
Определить максимальную и минимальную даты периода отчета. Вывод в переменную
Иногда требуется в отчете вывести период, за который он был сформирован. Например, отчет за предыдущую неделю.
В скрипте определяем переменную Прошлая Неделя:
LET vReportWeekPeriod = Num#((Week(Today())-1)&Year(Today()));
В календаре есть поле НеделяГод, с помощью этого поля определяем минимальную и максимальную даты (функция Date используется для форматирования даты в нужном виде, т.е. ‘DD.MM.YY’):
='Отчет. Период '& Date(min({<НеделяГод={'$(vReportWeekPeriod)'}>} Дата),'DD.MM.YY') &'-'& Date(max({<НеделяГод={'$(vReportWeekPeriod)'}>} Дата),'DD.MM.YY')
Пример использования агрегации данных по неделям (advanced scripting)
Если нужно упорядочивать записи по неделям, то рекомендую использовать формат ГодНеделя, причем неделя должна быть записана как 201901 и 201911 (т.е. два знака). В скрипте этот прием использован:
[TEMP Кол-во отгрузок По неделям]: LOAD [Клиент ID], Неделя, [КлиентНеделяID], count(DISTINCT [Документ ID]) As [Количество Отгрузок Неделя] Group By [Клиент ID], Неделя, [КлиентНеделяID]; LOAD Distinct [КлиентНеделяID], [Клиент ID], Num#(Year(Дата)&if(Len(Week(Дата))=1,'0'&Week(Дата),Week(Дата))) As Неделя, [Документ ID] Resident [Факты]; NoConcatenate [Номер отгрузки по неделям]: LOAD if([Клиент ID]=Previous([Клиент ID]), RangeSum(Peek('Номер отгрузки по неделям'), 1), 1 ) As [Номер отгрузки по неделям], [КлиентНеделяID] Resident [TEMP Кол-во отгрузок По неделям] Order By [Клиент ID], Неделя ; DROP Table [TEMP Кол-во отгрузок По неделям];
Создание переменных с датами
//Переменная Скрипта Qlik Sense - вчерашняя дата LET vTodayReport_Date = Date(Today()-1); //Переменная Скрипта Qlik Sense - дата "неделя назад" LET v7DaysAgoReport_Date = Date(Today()-7); //Переменная Скрипта Qlik Sense - дата "45 дней назад" LET v45DaysAgoReport_Date = Date(Today()-45); //Переменная Скрипта Qlik Sense - дата "месяц назад" LET v1MonthAgoReport_Date = AddMonths(Today(),-1); //Переменная Скрипта Qlik Sense - дата "3 месяца назад" LET v3MonthsAgoReport_Date = AddMonths(Today(),-3); //Переменная Скрипта Qlik Sense - дата "год назад" LET v1YearAgoReport_Date = AddYears(Today(),-1);
Добавить в формат даты неделю. Длинный формат месяца в дате Qlik Sense
Формат длинных названий месяцев задается на вкладке Main с помощью системных переменных. Значения этих переменных задается по-умолчанию при создании приложения:
SET MonthNames='янв.;февр.;мар.;апр.;мая;июн.;июл.;авг.;сент.;окт.;нояб.;дек.'; SET LongMonthNames='январь;февраль;март;апрель;май;июнь;июль;август;сентябрь;октябрь;ноябрь;декабрь';
Рекомендую заменить их на следующие значения (точки будут портить вид при формате DD.MMM.YYYY):
SET MonthNames='янв;фев;мар;апр;май;июн;июл;авг;сен;окт;ноя;дек'; SET LongMonthNames='января;февраля;марта;апреля;мая;июня;июля;августа;сентября;октября;ноября;декабря';
Формат даты с названием дня недели:
//Формат даты "11 июн 2019 (вт)" Date(Today(),'DD MMM YYYY (WWW)') //Формат даты "11 июн 2019 (вторник)" Date(Today(),'DD MMM YYYY (WWWW)') //Формат даты "11 июня 2019 (вторник)" Date(Today(),'DD MMMM YYYY (WWWW)')
Манипуляции над датами в скрипте через переменные
LET пГодУстановка = 2017; // 2017 LET пФильтрДата = MakeDate(пГодУстановка); // 01.01.2017 LET пФильтрДатаПрошлыйГод = MakeDate(пГодУстановка-1); // 01.01.2016
Шаг 4. Использование функций в выражениях (визуализации)
todo
Шаг 5. Кейсы/примеры работы с датами в Qlik Sense
Кейс 1. Обработка дат с английскими названиями месяцев
Данные хранятся в датах англоязычного календаря. Нужно произвести преобразование в русский формат даты:
Для преобразования используем следующий скрипт (с меппингом):
[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/Клик Сенс
- В выражениях диаграмм (в том числе в Set Analysis) стремитесь использовать переменные с датами. Это позволит минимизировать издержки на поддержку отчетов в Qlik Sense.
- …