Практика бизнес-анализа для BI-систем

Q: Вот уже 3й день борюсь с QlikView и пытаюсь придумать максимально эффективный метод формирования таблицы.
Итак, изначально есть QVD таблица, имеющая чуть более 600к записей, следующего типа:

triggerid time value
46397 2015-02-20 11:23:21 0
46397 2015-02-20 11:11:22 1
46397 2015-02-12 10:35:21 0
47654 2015-02-20 11:23:48 0
47654 2015-02-20 11:10:48 1
47654 2015-02-15 12:15:48 0

Нужно для каждого значения time, принадлежащего определенному triggerid, найти время следующего события, т.е. минимальное время из больших данного для данного triggerid.

Нужно получить следующую таблицу:

triggerid time value nexttime
46397 2015-02-20 11:23:21 0 null
46397 2015-02-20 11:11:22 1 2015-02-20 11:23:21
46397 2015-02-12 10:35:21 0 2015-02-20 11:11:22
47654 2015-02-20 11:23:48 0 null
47654 2015-02-20 11:10:48 1 2015-02-20 11:23:48
47654 2015-02-15 12:15:48 0 2015-02-20 11:10:48

Такие задачи обычно решаются через join, что я, собственно, и сделал.
Работает отлично, если я в запросе укажу небольшое кол-во triggerid. Однако при большом кол-ве, ОЗУ достаточно быстро забивается до отказа.

Реализовал загрузку следующим образом:
Код:
tmp:
LOAD
triggerid,
time as DownTime
FROM Table_events.qvd (qvd);

Left Join
LOAD
triggerid,
time as UpTime
FROM Table_events.qvd (qvd);

STORE tmp into Temp.qvd (qvd);
DROP Table tmp;

tmp:
LOAD
triggerid,
DownTime,
min(UpTime) as UpTime
from Temp.qvd (qvd)
Where DownTimeA: А если просто отсортировать список по тригерам и времени, и формировать новое поле с условием и peek()
Ведь отсортированный список подразумевает что следующее значение поля time для одного и того же тригера будет ближайшим минимальным из всего ряда значений времени для этого тригера.

Q: Добрый День! Подскажите как преобразовать числовое значение даты в секундах ( linux время) в нормальный формат, есть ли такая функция?

A: Дата — целое число, время — значение десятичной дроби, следовательно. имея метку времени, т.е. число с дробью для получения корректного значения даты требуется округлить в меньшую сторону.
Функция Floor()
Как вариант, можно использовать daystart()

Q: Возникла следующая задачка, не пойму как ее реализовать. Подкиньте, пожалуйста идейку.
У меня есть следующие поля:
— наименование акции
— даты проведения
— продажи в каждую дату
Пример тут https://yadi.sk/i/UKxngxf4ed6Ga

Что бы хотелось:
1. Подсчитать сколько дней длиться каждая акция. Например, 3 или 4 дня.
2. Для каждой акции проставить номер дня акции — 1й день, 2й день, 3й день и т.д. (может быть до 15 дней)
3. И в завершении сделать табличку вида:

Наименование акции 1й день 2й день 3й день и т.д.
Prior 68% 16% 14%
MM-dadak 75% 16% 8%
Лесная сказка 65% 19% 15%

A: Самое простое решение — формирование поля для хранения порядкового номера дня акции в скрипте.
Например:
Код:
tmp1:
LOAD * INLINE [
Акция,Дата,Продажи
Prior,01.02.2014,3000
Prior,02.02.2014,2500
Prior,03.02.2014,1000
MM-dadak,15.04.2014,5000
MM-dadak,16.04.2014,4000
MM-dadak,17.04.2014,2000
MM-dadak,18.04.2014,500
Лесная сказка,20.05.2014,3000
Лесная сказка,21.05.2014,1000
Лесная сказка,22.05.2014,300
];

NoConcatenate
LOAD *,
if(Акция<>Previous(Акция),1,Peek(ДеньАкции)+1) as ДеньАкции
Resident tmp1
Order by Акция, Дата;
DROP Table tmp1;

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

Q: Подскажите, пожалуйста, как сделать следующий расчет в скрипте? Во второй таблице рассчитываю сумму возврата, используя [Цена продажи,руб.] из первой таблицы. Или так нельзя?
Сейчас выдает ошибку при загрузке скрипта. Пример:

Заказы:
LOAD [№ заказа],
[ID товара],
[Кол-во товара в заказе,шт.],
[Цена продажи,руб.],
[Кол-во товара в заказе,шт.]* [Цена продажи,руб.] as [Сумма продажи,руб.]
FROM
[C:\Users\volodina.i\Desktop\QV Mamsy v.1 (янв-фев)\Заказ-товар.xlsx]
(ooxml, embedded labels, table is [Заказ-товар]);

Возвраты:
LOAD [Дата возврата],
[Кол-во возврата,шт.],
[№ заказа],
[Кол-во возврата,шт.]*[Цена продажи,руб.] as [Cумма возврата,руб.]
FROM
[C:\Users\volodina.i\Desktop\QV Mamsy v.1 (янв-фев)\Возвраты.xlsx]
(ooxml, embedded labels, table is Лист1);

A: Скорей всего, в заказах у вас комбинация уникальная [№ заказа], [ID товара].
В возвратах таких комбинаций может быть несколько.
Следовательно, по примеру piton грузим сначала таблицу возвратов, к ней присоединяем таблицу заказов. В итоге получим для каждой строки возврата сведения из строки заказов.

Без обработки в скрипте возможно получить сведения и выражениями, но только если построенная модель данных это позволит сделать.

Q: Есть задача.
Имеем таблицу календарь, и таблицу с данными:
Магазин,
Товар,
цена,
Начало цены,
Конец цены,
Далее надо сделать сводную таблицу(Chart) где строки будут магазин, товар. Столбцы будут дата из календаря(Аналитик хочет видеть цену на каждый день). В пересечении на каждую дату требуется вывести цену где, «Начало цены» больше или равно «Даты» и «Конец цены» меньше или равно «Даты».

Я вижу только решение объединить две таблицы вместе через left join. И при повторной обработки убрать записи через Where. Но таблица получается очень тяжелая. Есть ли другой способ не объединяя таблицы?

A: День добрый, посмотрите в документации описание и примеры по функции intervalmatch().

Q: Только начал использовать продукт, поэтому извините за возможно очевидный вопрос.
Загружаю данные из SQL Server через OLE DB. В одной из таблиц имеется поле с датой.
Эта дата никак не хочется распознаваться, как дата(
Встроенные функции, типа date(), нельзя применять в OLEDB запросе.
Пересохранить данные в файл, забрать оттуда и сконвертировать функцией — мне не нравиться это решение.
Подскажите пожалуйста верное решение.

A: Привет, а предварительную загрузку Load используете?
к примеру:
load date(datefield) as [Дата], …;
select sql from oledb;

Q: Возникла задача, для упрощения создал ограниченный пример. Есть мероприятия, у каждого мероприятия есть дата начала и дата окончания. Есть календарь. В календаре выбирается дата. Вопрос — как отфильтровать в таблице те мероприятия, у которых дата начала меньше выбранной даты, а дата окончания мероприятия больше выбранной даты. Во вложении картинка. Код приложения:

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 MonthNames=’янв;фев;мар;апр;май;июн;июл;авг;сен;окт;ноя;дек’;
SET DayNames=’Пн;Вт;Ср;Чт;Пт;Сб;Вс’;

Календарь:
load * Inline
[Дата, Квартал, Месяц, Год, День, Неделя, День недели];

Let ТекущаяДата = Num(Today(1));
Let ПрошедшийПериод = -10;
Let БудущийПериод = 0;

For i = $(ПрошедшийПериод) to $(БудущийПериод)
Concatenate(Календарь)
Load
Date ($(ТекущаяДата) + $(i)) as Дата,
‘Q’ & Ceil(Month($(ТекущаяДата) + $(i))/3) as Квартал,
Month (Date($(ТекущаяДата) + $(i))) as Месяц,
Year (Date($(ТекущаяДата) + $(i))) as Год,
Day (Date($(ТекущаяДата) + $(i))) as День,
week($(ТекущаяДата) + $(i)) as [Неделя],
weekday($(ТекущаяДата) + $(i)) as [День недели]
AutoGenerate 1
;
Next i;

Мероприятие:
Load * inline [
Дата старта мероприятия, Дата окончания мероприятия, Наименование мероприятия
01.01.2015, 05.01.2015, Мероприятие 1
01.01.2015, 03.01.2015, Мероприятие 2
02.01.2015, 06.01.2015, Мероприятие 3
02.01.2015, 04.01.2015, Мероприятие 4
03.01.2015, 03.01.2015, Мероприятие 5
03.01.2015, 05.01.2015, Мероприятие 6
04.01.2015, 05.01.2015, Мероприятие 7
05.01.2015, 10.01.2015, Мероприятие 8
06.01.2015, 11.01.2015, Мероприятие 9
];

A: Т.е. при выделении даты требуется отобрать записи мероприятий которые действуют на эту дату?
Обратите внимание на функцию IntervalMatch()
возьмите лет десять и сравните создание календарей на этот период в двух вариантах по времени и задействованным ресурсам.

Q: Привет, такой вопрос:

У меня есть поле в excel с таким содержанием:
Код:
http://www.сайт.ru/promo.html?utm_source=YD&utm_medium=cpc&utm_content=prodvizhenie&utm_campaign=obshhie_poisk_spb&yclid=5807162979809803946

Можно ли написать какой нить фильтр который выводил бы значения переменных из ссылки к примеру из utm_source=YD брал значение YD, и так далее по всем переменным в cсылке.

A: Конечно, как ни странно, но это тоже структурированная информация.
? — начало строки параметров
& — разделитель параметров
= — разделитель параметр/значение.
Думаю, для скрипта понадобится написание макроса.

Q: Доброго времени суток. Я только начинаю работать с qlikview и столкнулся с такой задачей которую не получается решить. Не подскажите как можно объединить повторяющиеся фразы в одну строку и сложить по ним расходы? (скриншот ниже).

A: Создайте объект диаграмма с измерением — Фраза и выражением для подсчета сумм
sum([Расход])
Тип диаграммы выбирается на вкладке «Общая» свойств диаграммы.
Выбираем иконку таблицы (нижний ряд, крайняя правая) Прямая таблица.

Q: Как обеспечить загрузку уникальных записей — аналог первичного ключа?

A: Если речь идет о загрузке уникальных значений поля из таблицы то следует использовать DISTINCT, например, код:
Код:
LOAD Distinct %ID INLINE [
%ID, A
0, 0
1, А
1, Б
1, В
2, Г
2, А
2, Б
3, В
3, Г
3, А
4, Б
4, В
4, Г
5, Д
5, Е
6, А
];
создаст таблицу :
%ID
1
2
3
4
5
6

Если идет речь о создании ключевого поля из комбинации полей, то в простых случаях можно использовать обычное сцепление текстовых значений, т.е. конструкция:
Код:
LOAD Distinct %ID&A as [%Key] INLINE
…
вернет уникальные записи типа 1А,1Б и т.д..

Также можно использовать одну из хэш-функций Qlikview: Hash128, Hash160, Hash256.
В этом случае мы также получим уникальный ключ для комбинации значений записи.
Код:
LOAD Distinct %ID&A as [%Key], Hash128(%ID,A) as %Key128, Hash160(%ID,A) as %Key160,Hash256(%ID,A) as %Key256 INLINE
Дает нам таблицу:
(Извините, но вы не имеете доступа к галерее)

Все вышесказанное справедливо и для Qlik SENSE,
ссылка на описание функций

Q: Хочу связать две таблицы. При этом хочу, чтобы из таблицы Клиенты (CUSTTABLE) выводились бы все значения вне зависимости от наличия для клиента плана (CUSTPLANCUSTOMERS). Пытаюсь сделать соединение, но лезут ошибки. Что делаю не так? Спасибо!

Код: [Выделить]
SQL SELECT КОД_КЛИЕНТА
FROM «MD_DynAXNewArc».dbo.CUSTTABLE;

SQL SELECT КОД_КЛИЕНТА,
AMOUNT as ‘СУММА ПЛАНА’,
TRANSDATE as ‘ДАТА’
FROM «MD_DynAXNewArc».dbo.CUSTPLANCUSTOMERS LEFT JOIN «MD_DynAXNewArc».dbo.CUSTTABLE ON «MD_DynAXNewArc».dbo.CUSTPLANCUSTOMERS.КОД_КЛИЕНТА= «MD_DynAXNewArc».dbo.CUSTTABLE.КОД_КЛИЕНТА;

A: Сталкивался как-то с тем, что отлаженный sql-запрос не работал через ODBC в Qlikview.
Но там было много условий и distinct.
В итоге просто взял нужные таблицы и обработал их уже средствами Клики.
Для начала, я бы проверил SQL — запрос не в Клике.

Q: Для дашборда нужно создать таблицу выбора месяцев.
При загрузке данных с access используется функция month([date]), все работает, только при таком решении месяца отображаются в числовом формате («6″,»7» и т.д)
Хотелось бы отображать в удобном текстовом («янв», «фев» и т.д.)
Когда на вкладке «Номер» выбираю «Дата» и MMM (вложение 114), в списке не происходит группировка и отображается множество записей «июнь»

Каким образом можно решить проблему?

A: Здесь есть пример по формированию календаря.
Если календарь не используется то придется календарные измерения формировать в таблице фактов.
На скриншоте показан скрипт загрузки данных из Access. Сразу бросается в глаза отсутствие операторов предварительной загрузки, load перед select.
В руководстве почитайте главы по загрузке данных.

Q: Помогите пожалуйста решить проблему:
В загружаемом из Excel столбце текст(точнее, цифры) разделены двоеточием. Необходимо, чтоб в модели этого разделителя-двоеточия не было. («11111:222» -> «11111222»). В исходном экселевском файле ничего менять нельзя. Как это сделать?

A: KeepChar(s1 , s2)
Возвращает строку s1 кроме символов, не содержащихся в строке s2.

Найти решение у бизнес-партнера QlikTech (QlikView) в России.

Форум разработчиков QlikView и Qlik Sense. Получите ответы на все вопросы по QlikView и Qlik Sense!