Неофициальный форум пользователей Qlik Sense & Qlikview

Для разработчиков => Вопросы по Qlik Sense & QlikView => Тема начата: Софья от 13 октября 2015, 11:15:22

Название: Автообновление данных на сервере и пересчёт предшествующего периода
Отправлено: Софья от 13 октября 2015, 11:15:22
Добрый день всем.

У меня очередная задача, и опять у меня много вопросов.

У меня есть 2 документа, которые входят в одно приложение. Один подгружает суммы, другой чеки за день (во вложениях Documents и Checks соответственно). Изначально думали, что они будут в одном скрипте, но так получилось, что пришлось их разделить. У нас непростое представление даты в системе, и если объединить их в одном коде, то получается наложение, как итог – данные некорректные, задвоенные + обилие синтетических ключей.

Есть также файл, куда отдельно выгружен календарь. Это сделано для того, чтобы каждый раз в скрипте не прогружались лишние поля. Для наглядности прикрепляю таблицу с ассоциированными полями, чтобы Вы видели, как коррелируют эти документы (вложение 1).

Всё работает, но возник вопрос, как организовать автообновление? Как Вы могли заметить, переменные с датой у меня статичны, и каждый раз, когда мне нужны данные за новый период, я их руками переписываю и загружаю.

В QMC я нашла вкладку Reload в разделе Documents, и у меня возник вопрос, как скорректировать данный код, чтобы после обновления у меня появлялись новые дни в приложении?

И чтобы при этом они так же загружались в исторический период, в qvd-файлы?

Вообще, конечная задача выглядит так:

Данные до определённой контрольной точки (даты) могут меняться. Именно поэтому необходимо, чтобы система каждый день в 6 часов утра начинала пересчитывать данные от контрольной точки до сегодняшней даты.

Допустим, контрольные точки – первое число каждого месяца. Если сегодня 13, то система с утра должна пересчитать данные до 11 числа + добавить 12 число. Если 25 октября, то принцип тот же самый. От первого до 23 пересчет + добавление в систему 24 числа, данных за вчера.  Наступает 1 ноября, контрольная точка, система должна пересчитать месяц и на этот раз уже записать его в qvd-файл, в исторические данные, после 1 ноября уже никто не может менять данные.

Старалась как можно детальнее обрисовать задачу, надеюсь, что доступно получилось. Заранее благодарна за любую информацию.

Documents: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='Пн;Вт;Ср;Чт;Пт;Сб;Вс';
SET vL1Date = "'12.10.2015 00:00:00','DD.MM.YYYY HH24:MI:SS'";     
SET vL2Date = "'12.10.2015 23:59:59','DD.MM.YYYY HH24:MI:SS'";
SET vDo1Date = "'10.10.2015 00:00:00','DD.MM.YYYY HH24:MI:SS'";     
SET vDo2Date = "'14.10.2015 23:59:59','DD.MM.YYYY HH24:MI:SS'";   

Документы:
LOAD ID AS ID_DOCUMENT,     
TYPE AS ТИП,   
DEPARTMENT AS ID_DEPARTMENT,
PARTN_DOC,
DOC_DATE,
SECOND(DOC_DATE) AS SECOND,           
MINUTE(DOC_DATE) AS MINUTE,
HOUR(DOC_DATE) AS HOUR,                         
DAY(DOC_DATE) AS DAY,
MONTH(DOC_DATE) AS MONTH,
YEAR(DOC_DATE) AS YEAR,
PRODUCT AS ID_PRODUCT,
F15007748 AS ЦЕНА,
F15007746 AS РОЗ_ЦЕНА,
F14286852 AS КОЛВО_ТОВАРА,
LINE_NO AS ЧЕК,
SF AS СУММА_ЗА_ДЕНЬ,
SR AS СУММА_РОЗ,
СКИДКА_В_РУБ,
RAZAND,
BEZNAL AS БЕЗНАЛ,
VOZVRAT AS ВОЗВРАТ,
POZ AS КОЛ_ВО_ПОЗИЦИЙ;     
SQL SELECT --+ INDEX(DB1_DOCUMENT DB1_DOCUMENT_BY_TYPE_DATE)
    D1.ID,
    D1.TYPE,
    D1.DEPARTMENT,
    D1.PARTN_DOC,
    TO_CHAR(TRUNC(L.F14286866), 'dd.mm.yyyy') AS DOC_DATE,
    L.PRODUCT,
    L.F15007748,
    L.F15007746,
    L.F14286852,
    L.LINE_NO,
    L.F15007748*L.F14286852 AS SF,
    L.F15007746*L.F14286852 AS SR,
    L.F15007746*L.F14286852-L.F15007748*L.F14286852 AS СКИДКА_В_РУБ,
    (L.F15007746*L.F14286852-L.F15007748*L.F14286852)*100 AS RAZAND,
    (SELECT CASE WHEN L2.F6684673 = HEXTORAW('0095000101BC0001') THEN 0 ELSE 1 END
    FROM ETK00."DB1_LINE" L2 WHERE L2.DOCUMENT = D1.ID AND L2.TYPE = 40304642 AND L2.LINE_NO = L.LINE_NO AND ROWNUM <= 1) AS BEZNAL,
    (CASE WHEN L.F14286852 > 0 THEN 0 ELSE 1 END) AS VOZVRAT,
    (SELECT COUNT(*) FROM ETK00."DB1_LINE" where DOCUMENT = D1.id and TYPE = 40304641 and LINE_NO = L.LINE_NO) AS POZ
FROM ETK00."DB1_DOCUMENT" D1, ETK00."DB1_LINE" L
WHERE D1.CLASS = 40304641
AND D1.TYPE = 40304641
AND D1.STATE = 1
AND L.TYPE = 40304641
AND D1.ID = L.DOCUMENT
AND D1.DOC_DATE >= TO_DATE($(vDo1Date))
AND D1.DOC_DATE <= TO_DATE($(vDo2Date))
AND L.F14286866 >= TO_DATE($(vL1Date))
AND L.F14286866 <= TO_DATE($(vL2Date));

STORE Документы INTO 12_OCTOBER.qvd (QVD);

Checks: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='Пн;Вт;Ср;Чт;Пт;Сб;Вс';
SET vL1Date = "'12.10.2015 00:00:00','DD.MM.YYYY HH24:MI:SS'";     
SET vL2Date = "'12.10.2015 23:59:59','DD.MM.YYYY HH24:MI:SS'";
SET vDo1Date = "'10.10.2015 00:00:00','DD.MM.YYYY HH24:MI:SS'";     
SET vDo2Date = "'14.10.2015 23:59:59','DD.MM.YYYY HH24:MI:SS'";     

Документы:
LOAD ID AS ID_DOCUMENT,     
DOC_DATE,
CCC AS КОЛВОЧЕКОВ;     
SQL SELECT D.ID,
    TRUNC(L.F14286866) AS DOC_DATE,
    COUNT(DISTINCT L.LINE_NO||d.partn_doc) AS CCC
FROM ETK00."DB1_DOCUMENT" D, ETK00."DB1_LINE" L, ETK00."DB1_AGENT" A1
WHERE D.CLASS = 40304641
AND D.TYPE = 40304641
AND L.TYPE = 40304641
AND D.ID = L.DOCUMENT
AND A1.ID = D.DEPARTMENT
AND D.DOC_DATE >= TO_DATE($(vDo1Date))
AND D.DOC_DATE <= TO_DATE($(vDo2Date))
AND L.F14286866 >= TO_DATE($(vL1Date))
AND L.F14286866 <= TO_DATE($(vL2Date))
GROUP BY d.id, TRUNC(L.F14286866);


Название: Re: Автообновление данных на сервере и пересчёт предшествующего периода
Отправлено: kvv от 13 октября 2015, 11:38:57
Добрый день!
В таких случаях использую функции: now(), today(), MonthEnd.
То есть, если сегодня 13.10.2015, то:
let v_Today = today();
let v_TodayMinus2 = today() - 2;
let v_TodayMinus1 = today() - 1;
let v_MonthEnd = MonthEnd(today());


И дальше уже настроить логику. Если не последний день месяца то менять данные и наоборот.
Как-то так.
Название: Re: Автообновление данных на сервере и пересчёт предшествующего периода
Отправлено: Софья от 13 октября 2015, 12:09:39
Искренне благодарю, буду пробовать, чуть позже отпишусь о результатах.
Название: Re: Автообновление данных на сервере и пересчёт предшествующего периода
Отправлено: Софья от 14 октября 2015, 12:20:39
Прописала даты через LET, как Вы сказали, - ошибка.
Название: Re: Автообновление данных на сервере и пересчёт предшествующего периода
Отправлено: kvv от 15 октября 2015, 09:23:27
Могу предположить, что нужно "поиграться" с форматом даты или с кавычками.
Смотрите второй скриншот - у меня так отрабатывает селект.
Название: Re: Автообновление данных на сервере и пересчёт предшествующего периода
Отправлено: Софья от 20 октября 2015, 06:47:09
Kvv, нашла у себя ошибку. Всё верно Вы сказали, проблема с форматом.

Если функция today() выдаёт дату, то у меня поле DOC_DATE дату хранит в таком виде:

SET vDate1 = "'17.08.2015 00:00:00','DD.MM.YYYY HH24:MI:SS'";     
SET vDate2 = "'17.08.2015 23:59:59','DD.MM.YYYY HH24:MI:SS'";


В моём случае время обязательно должно быть, т. к. документы не просто содержат дату, но и точное время, допустим, 19.11.2011 9:34:23.

Ваш скрин видела, принцип работы поняла, но я не могу у себя на входе задать переменную, которая содержит today() - ошибку как раз выдаёт ту, о которой писала.

Для наглядности прикреплю код и поясню принцип работы:
SKLAD:
SQL SELECT
    ID,
    CLASS,
    TYPE,
    DEBT_A1 AS ID_DEPARTMENT,
    D2,
    DOC_DATE,
    YEAR,
    MONTH,
    DAYNAMES,
    DAY,
    DEBT_A2 AS ID_PRODUCT,
    DEBT_A3 AS ID_PARTI,
    Z_CENA,
    0 AS FACT_CENA,
    KOL,
    KOL2,
    0 AS SUM_ZAK,
    0 AS SUM_ZAK2,
    0 AS SUM_FACT,
    0 AS SUM_FACT2,
    0 AS МАРЖА,
    0 AS DELIMOE,
    KOL-KOL2 AS OSTATOK   
FROM
(SELECT
    D.ID,
    D.CLASS,
    D.TYPE,
    T.DEBT_A1,
    D.DOC_DATE AS D2,
    TO_CHAR(D.DOC_DATE, 'dd.mm.yyyy') AS DOC_DATE,
    TO_CHAR(D.DOC_DATE, 'yyyy') AS YEAR,
    TO_CHAR(D.DOC_DATE, 'month') AS MONTH,
    TO_CHAR(D.DOC_DATE, 'day') AS DAYNAMES,
    TO_CHAR(D.DOC_DATE, 'dd') AS DAY,
    T.DEBT_A2,
    T.DEBT_A3,
    (SELECT P.F15007745 FROM DB1_PRODUCT P WHERE P.CLASS = 14745602 AND P.ID = T.DEBT_A3) AS Z_CENA,
CASE WHEN D.TYPE = 42402202 AND T.DEBT_A2 IS NOT NULL THEN T.AMOUNT
WHEN D.TYPE = 14286904 AND T.AMOUNT >0 THEN T.AMOUNT
         WHEN D.TYPE IN (3342376, 14286910, 14286911, 15925258, 6684753, 42401843, 14286901, 28835993, 655294492, 28835859, 14286903, 14286906) AND T.AMOUNT >0 THEN T.AMOUNT
         ELSE 0 END AS KOL,
    CASE WHEN D.TYPE = 40042591 AND T.DEBT_A1 IS NOT NULL THEN T.AMOUNT
    WHEN D.TYPE IN (14286904, 14286911, 655294492, 42401843, 14286901) AND T.AMOUNT <0 THEN -T.AMOUNT
    ELSE 0 END AS KOL2
FROM DB1_DOCUMENT D, DB1_TRANSACTION T
WHERE D.CLASS IN (14286850, 14286851)
AND D.TYPE IN (3342376, 14286910, 14286904, 14286911, 15925258, 6684753, 42401843, 14286901, 28835993, 655294492,
14286903, 14286906, 14286858, 14286853, 14286852, 14286862, 42401842, 28835859, 14286850, 3342375, 15925257, 6684754,
15138834, 655294493, 14286859, 42402202, 15138834, 42402075, 40042591, 14286854, 15138835, 57868291, 40042591)
AND D.STATE = 1
AND D.ID = T.DOCUMENT
AND T.CURRENCY = '0002002401AC0001'
AND D.DOC_DATE >= TO_DATE($(vDate1))
AND D.DOC_DATE <= TO_DATE($(vDate2))
)


На входе подаётся дата в формате, который прописан в SET - со временем. Здесь задаю не только дату, но и время, чтобы все документы за сутки вошли.

Потом обрабатываю DOC_DATE с помощью to_char, как раз чтобы получить просто дату, без времени. Естественно, на выходе я имею дату.

Но вот тут и главная проблема, как мне задать дату на входе? Есть ли подобные функции? Как today(), только чтобы было, допустим, начало дня, и время по умолчанию 0:00:00,  и конец дня, где время было бы 23:59:59?

Вся проблема как раз во времени, не будь бы его, Вашим способом легко решила бы проблему.
Название: Re: Автообновление данных на сервере и пересчёт предшествующего периода
Отправлено: kvv от 20 октября 2015, 09:40:42
Попробуйте функцию now().
Название: Re: Автообновление данных на сервере и пересчёт предшествующего периода
Отправлено: bibis от 20 октября 2015, 09:49:27
Добрый день. Честноговоря не очень понял затруднения и почему бы сразу в запросе не использовать
sysdate/current_date

Но по последней части вопроса, думаю подойдет
Timestamp( Today())        и    Timestamp( Today()+0.9999999)        соответственно.

Название: Re: Автообновление данных на сервере и пересчёт предшествующего периода
Отправлено: Софья от 20 октября 2015, 10:10:04
Цитата: bibis от 20 октября  2015, 09:49:27  
Добрый день. Честно говоря, не очень понял затруднения и почему бы сразу в запросе не использовать
sysdate/current_date

Имеете в виду, в SQL, когда на входе задаю для DOC_DATE переменные? В чём будет их преимущество?

Затруднения, что не понимаю часто, как SQL с QlikView в скрипте соотносить. Не всегда их функции и условия коррелируют. И глубокого понимания QV нет с SQL, разбираюсь понемногу, отсюда и вопросы дотошно-подобные.
Название: Re: Автообновление данных на сервере и пересчёт предшествующего периода
Отправлено: Софья от 20 октября 2015, 10:19:16
Цитата: kvv от 20 октября  2015, 09:40:42  
Попробуйте функцию now().

А как now() задать/обозначить, чтобы, допустим, от сегодняшней даты и времени, 20.10.2015 14:14:59 отнять период в 10 дней, но не до 14:14:59, а до 23:59:59?

Можно ли прописать другое время. Я так понимаю, что по умолчанию, если просто отниму 10 дней, то вторая дата получится 0 10.10.2015 14:14:59?

И где можно посмотреть информацию доступную по today(), now()  и т. д? В справке всё сухо написано, нюансы подобные не указаны.
Название: Re: Автообновление данных на сервере и пересчёт предшествующего периода
Отправлено: bibis от 20 октября 2015, 10:19:51
Ну мне просто непонятно , зачем вам вообще переменные задавать, если на первый взгляд хватит sqlя.

Ну а соотносятся они просто, делаем
load  ;  Перед запросом .
И уже внутри этого оператора  творим с полями всё что нужно.

По второму сообщению я выше уже писал формулу, как получить 23:59:
В данном случае =Timestamp( Today()+10.9999999)
А вообще просто поэксперементируйте с датами, округлениями, переводами в числовой формат и т.п. в справке написано достаточно,  нужно просто самому покнопать, чтоб "проникнуться" :)
Название: Re: Автообновление данных на сервере и пересчёт предшествующего периода
Отправлено: Софья от 20 октября 2015, 10:33:42
Цитата: bibis от 20 октября  2015, 10:19:51  
Ну мне просто непонятно , зачем вам вообще переменные задавать, если на первый взгляд хватит sqlя.

Цель конечная - сделать автоматическое обновление данных на сервере. Постоянно руками переписывать даты - это совсем не то, что нужно. Ну а чтобы подключить ежедневное автообновление в определённое время, нужно задать в скрипте переменные на вход динамичные, как today и today-14 (необходимо перезагружать период данный, т. к. в течение него данные меняются, чтобы корректными были). 

Получается, что из-за своего формата даты со временем я не могу применить today(), ибо он не подразумевает время. А привести данные к формату без времени - не проблема. Проблема их совместить, ибо на входе, когда задаю период для DOC_DATE, нужна дата в формате со временем.

Чувствую, что запутала вас всех. Как объяснить проблему - уже не знаю, вижу, что не могу донести доступно. Беда.

Название: Re: Автообновление данных на сервере и пересчёт предшествующего периода
Отправлено: DmitryK от 20 октября 2015, 10:41:01
С добрым утром, Софья!

А чем проблема со временем?
Вам надо брать период с 00:00:00 по 23:59:59

Возьмем период с первого по 14 число октября.
Используя today() мы тем самым стираем время и получаем просто дату 01.10.2015 и т.д.
Так 01.10.2015 - это весь день, т.е. берутся данные с 01.10.2015 00:00:00 по 23:59:59. Конечный дата 14.10.2015. Тут аналогично - данные за весь деть. Тем самым мы получаем данные с 01.10.2015 00:00:00 по 14.10.2015 23:59:59.

Т.е. 01.10.2015 00:00:00 <=DATE_DOC <= 14.10.2015 23:59:59
Т.е. DATE_START <=DATE_DOC <= DATE_END

С уважением,
Дмитрий
Название: Re: Автообновление данных на сервере и пересчёт предшествующего периода
Отправлено: bibis от 20 октября 2015, 10:41:21
Ну так вот я и спрашиваю, почему бы сразу в запросе не использовать
trunc(sysdate) = 00:00 сегодня   и trunc(sysdate)-14=00:00  2 недели назад
Название: Re: Автообновление данных на сервере и пересчёт предшествующего периода
Отправлено: Софья от 20 октября 2015, 12:11:02
Цитата: bibis от 20 октября  2015, 10:41:21  
Ну так вот я и спрашиваю, почему бы сразу в запросе не использовать
trunc(sysdate) = 00:00 сегодня   и trunc(sysdate)-14=00:00  2 недели назад

Не подумала об этом способе, попробую.
Название: Re: Автообновление данных на сервере и пересчёт предшествующего периода
Отправлено: Софья от 20 октября 2015, 12:14:46
Цитата: DmitryK от 20 октября  2015, 10:41:01  
С добрым утром, Софья!

А чем проблема со временем?
Вам надо брать период с 00:00:00 по 23:59:59

Возьмем период с первого по 14 число октября.
Используя today() мы тем самым стираем время и получаем просто дату 01.10.2015 и т.д.
Так 01.10.2015 - это весь день, т.е. берутся данные с 01.10.2015 00:00:00 по 23:59:59. Конечный дата 14.10.2015. Тут аналогично - данные за весь деть. Тем самым мы получаем данные с 01.10.2015 00:00:00 по 14.10.2015 23:59:59.

Т.е. 01.10.2015 00:00:00 <=DATE_DOC <= 14.10.2015 23:59:59
Т.е. DATE_START <=DATE_DOC <= DATE_END

С уважением,
Дмитрий

Я с Вами согласна, Дмитрий. Я также думаю. Но...у меня не работает today(), если подавать его на вход в SQL-запрос. Если прописать today() как переменные через LET, а потом добавить в него их - то же самое, ошибки, несоответствие формата.
Название: Re: Автообновление данных на сервере и пересчёт предшествующего периода
Отправлено: kvv от 20 октября 2015, 12:24:27
Софья, а попробуйте еще вот так:
let v_TodayMinus10 = Date(today() - 10) & ' 00:00:00';

P.s. Скорее всего, действительно, перестали "глубоко вникать" в данный вопрос, так как решение казалось простым... Но на самом деле, может быть по другому.
Название: Re: Автообновление данных на сервере и пересчёт предшествующего периода
Отправлено: Софья от 21 октября 2015, 08:56:08
Цитата: kvv от 20 октября  2015, 12:24:27  
Софья, а попробуйте еще вот так:
let v_TodayMinus10 = Date(today() - 10) & ' 00:00:00';

Добрый день.

Маленький прогресс есть - когда задала даты данным способом, начали работать переменные в выражениях. Раньше today() у меня вообще нигде не воспринимала программа, поэтому это уже что-то.

Спасибо Вам, Kvv.)

Но c DOC_DATE today() не хочет работать.

Задаю переменные перед таблицей:

let v_Today3 = Date(today() - 3) & ' 00:00:00';
let v_Today = Date(today()) & ' 00:00:00';


Подаю на вход полю DOC_DATE:

AND D.DOC_DATE > '$(v_Today3)'
AND D.DOC_DATE < '$(v_Today)'


Ошибка та же - литерал не соответствует формату строки.

Буду пробовать писать через SQL, как Bibis советовал.