Одним из первых вопросов, с которыми сталкивается новичок в Qlikview - анализ по датам, и прочим календарным измерениям.
Вопрос решается просто.
необходимо построить таблицу, содержащую записи на каждую дату, соответствующую календарному периоду анализируемого источника.
Начальная и конечная дата может быть задана переменными в сценарии, т.е. жестко в приложении.
Либо вычисляться через запрос к источнику данных.
Привожу пример для первого варианта:
//Формирование массива дат по вычисленным пределам
let vDate_start=MakeDate(2003,1,1);
LET Start = floor(vDate_start);
LET End = floor(YearEnd(AddMonths(today(),12)));
LET NumOfDays = End - Start + 1;
Date_src:
LOAD
$(Start) + Rowno() -1 as DateId
AUTOGENERATE $(NumOfDays); //массив дат в числовом формате
//Непосредственно календарь
Calendar:
LOAD
DateId,
year(DateId) as 'год',
dual('Кв.' & ceil(month(DateId)/3),ceil(month(DateId)/3)) as 'квартал',
dual('Кв.' & ceil(month(DateId)/3) & '-' & year(DateId),year(DateId) & ceil(month(DateId)/3)) as 'квартал-год',
month(DateId) as 'месяц',
ceil(month(DateId)) as 'месяц номер',
dual(month(DateId) & '-' & year(DateId),year(DateId) & num(month(DateId), '00')) as 'месяц-год',
week(DateId) as 'неделя',
weekday(DateId) as 'день недели',
date(DateId) as 'дата',
day(DateId) as 'день'
RESIDENT Date_src;
Drop Table Date_src;
Пример формирования календаря на основе исследуемых данных:
Dates:
LOAD * INLINE [
F1
01.03.2014
01.01.2014
31.12.2014
01.03.2015
];
tmp1:
LOAD min(F1) as 'DateStart', max(F1) as 'DateEnd' Resident Dates ;
LET Date_Start = peek('DateStart',0,'tmp1');
LET Date_End = peek('DateEnd',0,'tmp1');
LET vStart = floor(Date_Start);
LET vEnd = floor(Date_End);
LET vNumOfDays = vEnd - vStart + 1;
DROP Table tmp1;
Date_src:
LOAD $(vStart) + Rowno() -1 as DateId AUTOGENERATE $(vNumOfDays); //массив дат в числовом формате
//Непосредственно календарь
Calendar:
LOAD
DateId,
year(DateId) as 'год',
dual('Кв.' & ceil(month(DateId)/3),ceil(month(DateId)/3)) as 'квартал',
dual('Кв.' & ceil(month(DateId)/3) & '-' & year(DateId),year(DateId) & ceil(month(DateId)/3)) as 'квартал-год',
month(DateId) as 'месяц',
ceil(month(DateId)) as 'месяц номер',
dual(month(DateId) & '-' & year(DateId),year(DateId) & num(month(DateId), '00')) as 'месяц-год',
week(DateId) as 'неделя',
weekday(DateId) as 'день недели',
date(DateId) as 'дата',
day(DateId) as 'день'
RESIDENT Date_src;
Drop Table Date_src;
Вариант формирования календаря через функцию.
Источник - http://community.qlik.com/docs/DOC-5773 (http://community.qlik.com/docs/DOC-5773)
Не совсем понятно, как все таки построить календарь в QlikView )
Календарь надо загружать из Excel?
(Для понимания изложенного ниже материала рекомендую открыть Qlikview или Sense и создав новое приложение пошагово изучить работу описанного ниже процесса получения данных по курсу валют с календарем)
Календарь - таблица с колонками, необходимыми для вашей задачи.
Например, если вас интересует только динамика по дням и никаких вычислений в разрезе других календарных измерений не требуется, то и смысла в календаре мало.
Но если необходимо производить фильтрацию по годам, месяцам и пр. то необходимо сделать таблицу, с вычисленными календарными измерениями (год, квартал и т.д.).
Например, вы хотите провести анализ по динамике курса валюты и расчет среднего курса на каждый месяц.
Берем код отсюда (http://qlikview-forum.ru/qvf/index.php/topic,442.msg809.html#msg809).
Вставляем в сценарий своего приложения и правим даты по своему требованию.
Например
//Вычисляем переменные для вставки в сторке параметров
let vDateStart = date('01.01.2015','DD/MM/YYYY');
let vDateEnd = date(today(),'DD/MM/YYYY');
//Строка параметров
set vURLHist='http://www.cbr.ru/scripts/XML_dynamic.asp?date_req1=$(vDateStart)&date_req2=$(vDateEnd)&VAL_NM_RQ=';
//Код валюты
set vVALID='R01235';
//Грузим курсы валют
TmpRate:
LOAD Date,Id,Nominal,Value
FROM [$(vURLHist)$(vVALID)] (XmlSimple, Table is [ValCurs/Record]);
//Определяем границы периода, т.к. они могут отличаться от заданной установки в запросе
TmpDate:
Load Min(Date) as MinD, Max(Date) as MaxD resident TmpRate;
// Переносим значения из таблицы в переменную
Let vMinD = Peek('MinD',-1,'TmpDate')-1;
Let vMaxD = Peek('MaxD',-1,'TmpDate');
// Удаляем временную таблицу с границами периода
Drop Table TmpDate;
//Устанавливаем связь с таблицей из одного поля Date, но со всеми датам в диапазоне vMaxDate, vMinDate
Join (TmpRate) Load Date(recno()+$(vMinD)) as Date Autogenerate vMaxD-vMinD;
// Здесь мы получаем таблицу, на все даты, но для некоторых дат значения полей Id,Nominal,Value отсутствуют
//Далее мы заполняем пустые значения данными за предыдущий день
КурсыВалют:
NoConcatenate Load Date as Дата,
If(IsNull(Id), Peek(Id), Id) as Id,
If(IsNull(Nominal), Peek(Nominal), Nominal) as Nominal,
if(IsNull(Value), Peek(Value), Value) as Value
Resident TmpRate
Order By Date ;
DROP Table TmpRate;
// В итоге получаем таблицу с курсом валюты на каждый день
Далее, обратите внимание, что границы периода уже заданы нами же в переменных, следовательно определять границы дат запросами не имеет смысла.
Поэтому мы просто :
//Вычисляем требуемое количество записей для календаря
LET NumOfDays = vMaxD-vMinD+1;
//Формируем таблицу с числовыми значениями дат на весь период
tmp_Date:
LOAD $(vMinD) + Rowno() -1 as DateId AUTOGENERATE $(NumOfDays);
На этом этапе мы получили таблицу с одним полем и числом записей, равным числу дней периода.
И нам теперь предстоит каждое числовое значение даты обработать календарными функциями и получить массив дат и соответствующих измерений:
//Формируем сам календарь, где каждое числовое значение даты обрабатывается календарными функциями для полчения необходимого значения.
Календарь:
LOAD
DateId,
year(DateId) as 'Год',
month(DateId) as 'Месяц',
ceil(month(DateId)) as 'Месяц номер',
dual(month(DateId) & '-' & year(DateId),year(DateId) & num(month(DateId), '00')) as 'Месяц-год',
date(DateId) as 'Дата'
RESIDENT tmp_Date;
//Удаляем временную таблицу
Drop Table tmp_Date;
Все функции подробно описаны на сайте help.qlik.com
Функции даты и времени (http://help.qlik.com/sense/ru-RU/online/#../Subsystems/Hub/Content/Scripting/DateAndTimeFunctions/DateAndTimeFunctions.htm%3FTocPath%3D%25D0%25A0%25D0%25B0%25D0%25B1%25D0%25BE%25D1%2582%25D0%25B0%2520%25D1%2581%2520Qlik%2520Sense%7C%25D0%25A1%25D0%25B8%25D0%25BD%25D1%2582%25D0%25B0%25D0%25BA%25D1%2581%25D0%25B8%25D1%2581%2520%25D1%2581%25D0%25BA%25D1%2580%25D0%25B8%25D0%25BF%25D1%2582%25D0%25B0%2520%25D0%25B8%2520%25D1%2584%25D1%2583%25D0%25BD%25D0%25BA%25D1%2586%25D0%25B8%25D0%25B8%2520%25D0%25B4%25D0%25B8%25D0%25B0%25D0%25B3%25D1%2580%25D0%25B0%25D0%25BC%25D0%25BC%25D1%258B%7C%25D0%25A4%25D1%2583%25D0%25BD%25D0%25BA%25D1%2586%25D0%25B8%25D0%25B8%2520%25D0%25B2%2520%25D1%2581%25D0%25BA%25D1%2580%25D0%25B8%25D0%25BF%25D1%2582%25D0%25B0%25D1%2585%2520%25D0%25B8%2520%25D0%25B2%25D1%258B%25D1%2580%25D0%25B0%25D0%25B6%25D0%25B5%25D0%25BD%25D0%25B8%25D1%258F%25D1%2585%2520%25D0%25B4%25D0%25B8%25D0%25B0%25D0%25B3%25D1%2580%25D0%25B0%25D0%25BC%25D0%25BC%25D1%258B%7C%25D0%25A4%25D1%2583%25D0%25BD%25D0%25BA%25D1%2586%25D0%25B8%25D0%25B8%2520%25D0%25B4%25D0%25B0%25D1%2582%25D1%258B%2520%25D0%25B8%2520%25D0%25B2%25D1%2580%25D0%25B5%25D0%25BC%25D0%25B5%25D0%25BD%25D0%25B8%7C_____0)
Для понимания принципов работы скрипта скопируйте блоки в один сценарий поочередно и на каждом этапе проверяйте результат, так легче понимать работу программы, на любом языке.
И в завершении, все на тему календарей в Qlik-сообществе (http://community.qlik.com/search.jspa?q=calendar)
Отличная статья в блоге о Qlikview (http://blog.atkcg.ru/kalendar-v-qlikview-razlozhim-po-polochkam/) (Внимание! Файлы, предлагаемые для скачивания по ссылке не предназначены для просмотра в персональной версии.)
Отличный инструмент.
Осталось только понять логику применения когда есть база с несколькими полями дат. Ведь кликвью не допускает в одной таблице несколько полей с одним наименованием?! Правильно ли я понимаю, что мне придется разбить исходную базу и загрузить ее столько раз сколько полей с датами у меня есть для возможности представления различных данных в единицах временного периода в соответствие с календарем?