Загрузка входных данных (остатки) на начало каждого года в Qlikview (Qlik Sense)

Автор Софья, 23 июля 2015, 01:20:30

« назад - далее »

Софья

Всем добрый день.
Просмотрела форум, не нашла подобной темы. Если она уже обсуждалась здесь, и я просто была невнимательна, прошу перенаправить на неё.
Теперь о проблеме.
Приложение разрабатываемое - складской учёт/товародвижение. Это количество прихода и расхода по каждому товару за день. Отчётность ведётся с 2004 года. Фактически, детализация по дням и продуктам нужна за последние 2 года. Приложение с данными за год занимает почти 4 ГБ. В этом и основная проблема, будь бы объём данных меньше, можно было прогрузить все года. Но 4 ГБ*12 лет = 64 ГБ. Сами понимаете, объём отчёта получится сказочный, да и как будет реагировать выборка - даже не хочется задумываться.
Вопрос: можно но ли прогрузить данные на конец каждого года в виде таблицы-столбца? Иными словами, остаток по каждому товару на конец каждого года? Допустим, на конец 2014 года мы имеем:
Бананов - 48 кг;
Салфеток - 67 шт и т. д.
Заранее отвечаю на вопрос - зачем это нужно. Суть в том, что товародвижение каждого последующего года цепляется за предыдущий, и если я прогружу только последние 2 года, то отображение остатков будет некорректно по всем видам товаров.
Про формирование QVD-файла знаю, активно пользуюсь данным. Но вот весь объём данных мне не нужен, который он прогружает в виде таблицы, нужно только итоговое количество на конец каждого года. Возможно ли это осуществить и с помощью какой функции/операторов?
Буду благодарна любой информации по данному вопросу.

kvv

Добрый день.
Если правильно понял, то можно при загрузке данных делать фильтр вида:
Load товар, дата, стоимость
From ОтКудаТо
Where дата = '31.12.2004' or дата = '31.12.2005' or ... or дата = '31.12.2012' or дата >= '01.01.2013'

admin

День добрый.
ЦитироватьИными словами, остаток по каждому товару на конец каждого года?
Конечно ДА.
Цитироватьи если я прогружу только последние 2 года, то отображение остатков будет некорректно по всем видам товаров
Т.е. таблицы остатков у вас нет, и сальдо считается в приложении?

Софья

Kvv, не подумала об этом, попробую.

Admin, таблица остатков есть, но она некорректна, как показали всевозможные инвентаризации и сверки с документами.
Суть в том, что всё равно нужно мне прогрузить все эти года, чтобы просчитать корректно остатки, только с 2004 по 2013 мне не нужны детальные таблицы - только итоговые числа. Чтобы получались таблицы на конец каждого года, как во вложении.
Получается, загружаю 2004, получаю данные. Подключаю эти данные к 2005 году, что на начало 2005 года 48 кг бананов. Это должно аккумулироваться, и с учётом 2004 и 2005 года, допустим, у меня на конец 2005 года получается 119 кг бананов. Подключаю этот столбец к 2006 и т. д.
Суть в этом, чтобы получать таблицу с итоговыми числами, которая занимает минимальный объём и содержит пару полей всего, потому что 2014 и 2015 мне придётся прогрузить, и приложение уже занимает почти 8 ГБ.
Надеюсь, я внятно объясняю, не запутала никого.)

bibis

Ну так прогрузить один раз, выявить последний остаток по каждому товару .
   Соответственно  табличка с 2я полями:товар, остаток. А последние 2 года уже нормально грузить, свяжутся таблицы по названию товара,можно будет прибавлять последний остаток к текущему.


admin

Цитировать
таблица остатков есть, но она некорректна, как показали всевозможные инвентаризации и сверки с документами.
Какая учетная система?

Софья, вы формируете хранилище QVD файлов?
Если нет, то отличным выходом будет расчет остатков по реестрам движений и размещение их в хранилище данных. И детальных, и месячных.
В этом случае в приложениях ничего не надо считать, просто подтягивайте нужные данные и все.
При этом файлы можно разбить по годам, что упростит загрузку.

Софья

Bibis, про прогрузку меньшего количества полей - хорошая идея, что-то мне сразу в голову не пришло. Ищу сейчас информацию, чтобы максимально уменьшить объём приложений и время их загрузки, т. к. один год загружается почти 3 дня. А весь период будет загружаться в районе 40 дней. Думаю, объяснять ничего не нужно.)

Admin, старая учётная система - OLAP. Я её не разрабатывала, её поддерживала сторонняя организация, поэтому в её тонкостях, можно сказать, не разбираюсь.
Я формирую QVD-файлы по каждому периоду, один месяц - один файл. А после добавляю их в скрипт на обработку.
А что представляет из себя QVD-хранилище, как его организовать?
Оговорюсь, что сейчас компания на этапе покупки лицензий, поэтому пока я работаю на бесплатной версии. Возможно ли в данном случае организовать подтягивание данных, и если можно, то каким образом?
А приход и расход как раз включают в себя типы движений, в каждый в районе 10 (скрин во вложении). Остатки, соответственно, объединяют все виды движений в единые показатели - поле по приходу и поле по расходу.

admin

Привет.
С учеткой придется разобраться. Как вы к ней подключаетесь и получаете данные?
Хранилище в данном случае - структура папок с файлами QVD формата, которые формируются приложением-скриптом без визуализации.
Его можно делать и на бесплатной версии. Ставите на сервер и прописываете скрипт загрузки данных.
Вопрос довольно сложный. Здесь лучше довериться специалистам.
Раз вы в стадии покупки лицензий, то наверняка кто-то возьмется за внедрение.
Если нет, то самостоятельно можно просто затянуть с получением результата и "уйти не туда".

admin

Цитата: Софья от 24 июля  2015, 05:44:18  
Оговорюсь, что сейчас компания на этапе покупки лицензий, поэтому пока я работаю на бесплатной версии. Возможно ли в данном случае организовать подтягивание данных, и если можно, то каким образом?
А приход и расход как раз включают в себя типы движений, в каждый в районе 10 (скрин во вложении). Остатки, соответственно, объединяют все виды движений в единые показатели - поле по приходу и поле по расходу.
Можете подготовить тестовый фрагмент данных, чтобы обсудить расчет остатков - тема очень интересная и актуальна для большинства.

Софья

Все данные хранятся в БД Oracle, к ней я подключаюсь с помощью OLE DB в QlikView.
OLAP тоже подключён непосредственно к ней, в Visual Studio прописаны все функции и команды. Получается, поддерживали её специалисты, отладили и отпустили нашу клмпанию в свободное плавание. Сейчас все просто пользуются ранее созданными отчётами, но внутрь никто не залазит.
Поэтому ещё одна из задач, которая на мне - повторить все эти отчёты, только в другой системе, в QlikView. Чтобы в компании, был штатный специалист, который прописал это и понимал, откуда берутся данные.
Поэтому работаю непосредственно с Oracle Developer, там прописываю необходимые операции и запросы, а потом переношу в QlikView.
С внедрением, конечно, попрошу помощи. Уж лучше корректно всё установить, чем наломать дров и устранять потом все неисправности.

Конечно, если кому-то могу быть полезной - я только рада.

Склад:
LOAD ID AS ID_DOCUMENT,   
    CLASS,   
    TYPE AS ТИП,   
    ID_DEPARTMENT,
    DOC_DATE,
    SECOND(DOC_DATE) AS SECOND,           
    MINUTE(DOC_DATE) AS MINUTE,
    HOUR(DOC_DATE) AS HOUR,                         
    DAY(DOC_DATE) AS DAY,
    WEEKDAY(DOC_DATE) AS DAYNAMES,
    MONTH(DOC_DATE) AS MONTH,
    YEAR(DOC_DATE) AS YEAR,
    ID_PRODUCT,
    ID_PARTI,
    KOL2 AS РАСХОД_ШТ,
    KOL AS ПРИХОД_ШТ,
    OSTATOK;
SQL SELECT
    ID,
    CLASS,
    TYPE,
    DEBT_A1 AS ID_DEPARTMENT,
    D2,
    DOC_DATE,
    DEBT_A2 AS ID_PRODUCT,
    DEBT_A3 AS ID_PARTI,
    KOL,
    KOL2,
    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,
    T.DEBT_A2,
    T.DEBT_A3,
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 = 14286904 AND T.AMOUNT >0 THEN T.AMOUNT
     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))
)

UNION ALL

SELECT
    ID,
    CLASS,
    TYPE,
    CRDT_A1 AS ID_DEPARTMENT,
    D2,
    DOC_DATE,
    CRDT_A2 AS ID_PRODUCT,
    CRDT_A3 AS ID_PARTI,
    KOL,
    KOL2,
    KOL-KOL2 AS OSTATOK
FROM
(SELECT D.ID,
    D.CLASS,
    D.TYPE,
    T.CRDT_A1,
    D.DOC_DATE AS D2,
    TO_CHAR(D.DOC_DATE, 'dd.mm.yyyy') AS DOC_DATE,
    T.CRDT_A2,
    T.CRDT_A3,
CASE WHEN D.TYPE = 42402202 AND T.CRDT_A2 IS NOT NULL THEN T.AMOUNT
     WHEN D.TYPE = 655294492 AND T.AMOUNT <0 THEN -T.AMOUNT
     WHEN D.TYPE = 655294492 AND T.AMOUNT >0 THEN 0
     WHEN D.TYPE IN (3342375, 6684754, 14286850, 14286853, 14286859, 14286862, 15138834, 15925257, 28835859, 42401842, 14286904, 14286911, 15138834, 42402075, 14286854, 15138835, 57868291, 40042591, 655294493) AND T.AMOUNT >0 THEN T.AMOUNT
     ELSE 0 END AS KOL2,
CASE WHEN D.TYPE = 42402202 AND T.CRDT_A2 IS NULL THEN T.AMOUNT
     WHEN D.TYPE = 28835859 AND T.AMOUNT >0 THEN T.AMOUNT
     WHEN D.TYPE IN (14286858, 14286852, 655294493, 655294492, 57868291, 42401842) AND T.AMOUNT <0
     THEN -T.AMOUNT
     ELSE 0 END AS KOL
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,
14286858, 14286853, 14286852, 14286862, 42401842, 28835859, 14286850, 3342375, 15925257, 6684754, 15138834, 655294493,
14286859, 42402202, 15138834, 42402075, 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)));

Store Склад into 1.qvd (QVD);


А теперь сами пояснения к коду:
Данные берутся с таблиц, в данном случае из таблицы документов и таблицы транзакций. Думаю, поля с ID в начале названия не нужно пояснять.
Более подробно расскажу о товародвижении. У каждого типа движения свой тип документа. Как могли обратить внимание, запрос связан Union - первая часть кода относится к приходным документам, а вторая к расходным. Как по приходным документам, так и по расходным есть приход и есть расход. Отсюда и берётся это гигантское перечисление типов документов, потому что часть из них образует показатели по столбцу прихода, а вторая часть по столбцу расхода - соответственно KOL и KOL2. Таблица транзакций отображает все движения по каждому виду документа/товара, поэтому и подключена здесь - из неё формируется количество товара, T.AMOUNT.
Поля из таблицы транзакций содержат в себе информацию о подразделениях, виде товара, поэтому они названы как одноимённые поля из таблиц, которые содержат данную информацию ID_DEPARTMENT/ID_PRODUCT. По этим ключевым подям уже ижёт связь с другими таблицами, которые содержат необходимую для аналитики информацию.
Думаю, основное озвучила.

DmitryK

Добрый день коллеги) Только недавно закончил заниматься такой же задачей)
У меня данные первый раз грузились с 2013 года по июль 2015. Согласен...действительно долгий процесс (1.5 дня).
Предлагаю на рассмотрение мой вариант решения задачи (постараюсь максимально "разжевать" все):
Directory

// Загружаю исходные данные (первичная обработка)

IF $(ReLoadStockAXData) = 1 then

// У меня инкрементальная загрузка (Сейчас скрипт обрабатывает данные за целый день ~100000 строк за 5-10 минут). Поэтому ищем дату последнего изменения в системе. (в конце сохраняем её в QVD)

ModifiedDateTable:
Load MaxModDateTime as MaxModDateTime
FROM StockAX\ModifiedDateTable.qvd(qvd);

LET MaxModDateTimeTmp = PEEK('MaxModDateTime'); // Сохраняем Дату в переменную
LET MaxModDateTime=date('$(MaxModDateTimeTmp)','YYYY-MM-DD h:mm:ss[.fff]');
// Задаем формат как в системе
drop table ModifiedDateTable;

Tmp_1: //Грузим данные из системы
// Дата и время изменения >= Максимальной даты и времени изменения
SELECT Concat(it.ITEMID,'',ind.INVENTSIZEID,'',ind.INVENTLOCATIONID,'',ind.INVENTSALESCHANNELID) as 'ItemInventDimIdKey'
,isnull(convert(int,it.DATEPHYSICAL), convert(int,it.DATEFINANCIAL))+2  as [№ДатыСклад]
,it.ITEMID as '%Номенклатура'
,ind.INVENTLOCATIONID as '%Склад'
,ind.INVENTSIZEID as '%Характеристика'
,ind.INVENTSALESCHANNELID as '%КаналПродаж'
,it.QTY as _Количество
,it.COSTAMOUNTADJUSTMENT+it.COSTAMOUNTPOSTED as _Себестоимость
,it.MODIFIEDDATETIME as MODIFIEDDATETIME
,it.RECID
FROM INVENTTRANS as it /* Складские проводки */
left join INVENTDIM as ind ON /* Складские аналитики */
it.INVENTDIMID = ind.INVENTDIMID
and it.PARTITION = ind.PARTITION
WHERE   it.MODIFIEDDATETIME >= '$(MaxModDateTime)';

// Выгружаем новую дату для след. PreLoad
ADD NoConcatenate
ModifiedDateTable:
Load date(MaxModDateTime,'YYYY-MM-DD h:mm:ss[.fff]') as MaxModDateTime;
Load max(MODIFIEDDATETIME) as MaxModDateTime
Resident Tmp_1;

ADD NoConcatenate
Tmp: /*** Группируем данные ***/
Load 003&ItemInventDimIdKey,
003&AutoNumberHash128(ItemInventDimIdKey, 'Ctrl3') AS Key, //Основной ключик для обработки, на остальные можно не смотреть.
№ДатыСклад,
%Номенклатура,
%Склад,
%Характеристика,
%КаналПродаж,
     sum(_Количество) as _Количество,
     sum(_Себестоимость) as _Себестоимость
Resident Tmp_1
WHERE not isnull(№ДатыСклад)
Group by №ДатыСклад, %Номенклатура, %Склад, %Характеристика, %КаналПродаж, ItemInventDimIdKey
;

store Tmp into StockAX\StockAXTmp.qvd(qvd);
drop tables Tmp_1, Tmp;

/*** Start. "Формирование остатков на дату" ***/

TSK: // Определяем количество шагов для обработки данных по частям (обработка всего объема данных приводит к съеданию всего ЦП и ОЗУ - а нам это надо?! :-) Я беру по 1 000 000 записей в обработку)
Load round(Count(DISTINCT KeyG)/1000000,1)+1 as StepKey
FROM StockAX\StockAXTmp.qvd
(qvd);

Let StepKey = num(peek('StepKey',0,'TSK')); // Сохраняем количество шагов в переменную
LET g = 1; //Переменная для указания количества выгружаемых уникальных значений для обработки

drop Table TSK;

T1Tmp: // ВСЕ данные из ранее сгенерированного QVD
Load *
FROM StockAX\StockAXTmp.qvd
(qvd);

Stock_info: // Справочник кодов по SKU, складам и др. Гружу, чтобы не болтались при обработке и не нагружали будущую  ТФ. Я гружу полностью, не запариваюсь с PreLoad и Exists().
SELECT distinct Concat(it.ITEMID,'',ind.INVENTSIZEID,'',ind.INVENTLOCATIONID,'',ind.INVENTSALESCHANNELID) as 'ItemInventDimIdKey',
it.ITEMID as '%Номенклатура',
ind.INVENTLOCATIONID as '%Склад',
ind.INVENTSIZEID as '%Характеристика',
ind.INVENTSALESCHANNELID as '%КаналПродаж'
FROM INVENTTRANS as it
left join INVENTDIM as ind ON
it.INVENTDIMID = ind.INVENTDIMID
and it.PARTITION = ind.PARTITION
left join INVENTTABLE ON
INVENTTABLE.ITEMID = it.ITEMID
and INVENTTABLE.PARTITION = it.PARTITION
and INVENTTABLE.PRODUCTTYPE = 1
WHERE   it.STATUSISSUE in (1,0)
and it.STATUSRECEIPT in (1,0)
and it.PARTITION = 5637145328;

Store Stock_info Into ОстаткиНаСкладах_Инфо.qvd(qvd); // Сохраняем справочник кодов
Drop Table Stock_info;

/*** Цикл обработки данных по шагам ***/

FOR i=1 to $(StepKey)

// Будущая таблица фактов. Данные для обработки. Ограничиваем загрузку с помощью переменной $(g) на 1 Млн. уникальных значений поля Key.
// От поля Key убираем первые 3 цифры "003", которые явл. идентификатором для розницы.

T1:
NoConcatenate
Load ItemInventDimIdKey,
Key,
Key as ExistsKey,
№ДатыСклад,
_Количество,
     _Себестоимость
Resident T1Tmp
WHERE   NUM(mid(KeyG,4))>=$(g)
and NUM(mid(KeyG,4))<=$(g)+1000000;

LET g = $(g)+1000000;

//Подготавливаем перемменую G для след шага.

RENAME Table T1Tmp to T1Tmp_TEMP;
//Переименновываем исходную таблицу данных, формируя "времменую"';

// Формируем новую исходную таблицу, исключая выгруженные раннее значения Key';
T1Tmp:
NoConcatenate
Load * Resident T1Tmp_TEMP
WHERE not Exists(ExistsKey,Key);

Drop table T1Tmp_TEMP; // Удаляем "времменую"

// Находим сумму, мин, макс даты (если остаток у Key = 0, то беру макс дату по №ДатыСклад (Зачем нам нули? Например с 13-го года???) ) для каждого уникального значения поля Key,
T2:
Load Key,
Min№ДатыСклад,
IF(_КоличествоTotal = 0, Max№ДатыСклад, Today()) as Max№ДатыСклад,
_КоличествоTotal;
Load Key,
min(№ДатыСклад) as Min№ДатыСклад,
max(№ДатыСклад) as Max№ДатыСклад,
sum(_Количество) as _КоличествоTotal
Resident T1
Group by Key;

// Проставляем (добавляем строки) даты ('№ДатыСклад2') между Min№ДатыСклад и Max№ДатыСклад. Условие:';
T3:
Load Key,
Min№ДатыСклад,
Max№ДатыСклад,
num(Min№ДатыСклад+IterNo()-1) as №ДатыСклад2
resident T2
While (Min№ДатыСклад+IterNo()-1)<=Max№ДатыСклад;

drop table T2;

// джоиним значения Key к новым датам';
join(T3)
Load FieldValue('Key',RecNo()) as Key
AutoGenerate FieldValueCount('Key');

//Повторно грузим мин и макс значения';
T6:
Load Key,
Min№ДатыСклад,
IF(_КоличествоTotal = 0, Max№ДатыСклад, Today()) as Max№ДатыСклад;
Load Key,
min(№ДатыСклад) as Min№ДатыСклад,
max(№ДатыСклад) as Max№ДатыСклад,
sum(_Количество) as _КоличествоTotal
Resident T1
group by Key;

// Проставляем даты';
inner join(T3)
IntervalMatch(№ДатыСклад2,Key)
Load Key,
Min№ДатыСклад,
Max№ДатыСклад
Resident T6;

drop table T6;

// добавляем Даты в таблицу фактов';
join(T1)
Load Key,
№ДатыСклад2 as №ДатыСклад
Resident T3;

drop table T3;

// Заполняем значения';
T4:
Load IF(peek(Key)=Key, peek('ItemInventDimIdKey'), ItemInventDimIdKey) as ItemInventDimIdKey,
    Key,
    floor(№ДатыСклад) as №ДатыСклад,
    num(IF(len(trim(_Количество))=0 and peek(Key)=Key,0, _Количество)) as _Количество,
    num(IF(len(trim(_Количество))=0 and peek(Key)=Key, alt(peek('_КоличествоИтогОстатки'),0),
    IF(peek(Key)=Key,_Количество + alt(peek('_КоличествоИтогОстатки'),0),_Количество))) as _КоличествоИтогОстатки,
    num(IF(len(trim(_Себестоимость))=0 and peek(Key)=Key,0, _Себестоимость), '###0,00') as _Себестоимость,
    num(IF(len(trim(_Себестоимость))=0 and peek(Key)=Key, alt(peek('_СебестоимостьИтогОстатки'),0),
    IF(peek(Key)=Key,_Себестоимость + alt(peek('_СебестоимостьИтогОстатки'),0),_Себестоимость)),'###0,00') as _СебестоимостьИтогОстатки
resident T1
order by Key,№ДатыСклад;   

//Формируем доп поле для обработки лишних данных + исключаем строки где присутствуют нулевые остатки';
T5:
NoConcatenate
Load *, Key as ExistsKey/*, Key&floor(№ДатыСклад) as LastDateMonthKey*/
Resident T4
WHERE _КоличествоИтогОстатки<>0;

drop table T4;

// Пошагово сохраняем';
Store T5 Into StockAX\Stock_step$(i).qvd(qvd);

Drop table T1;
drop table T5;

next i
/*** End. Цикл обработки данных по шагам ***/

drop tables T1Tmp;

/*** Формируем единую QVD по остаткам в рознице из сгенерированных раннее***************************************************/
Stock:
Load ItemInventDimIdKey,
Key,
Key as KeySt,
floor(№ДатыСклад) as №ДатыСклад,
     _Количество,
     _КоличествоИтогОстатки,
     _Себестоимость,
     _СебестоимостьИтогОстатки
FROM
[StockAX\Stock_step1.qvd]
(qvd);

Add NoConcatenate // Чтобы не забивать место на диске - чищу сразу QVD.
QVDClearTable1:
Load *, '' as Clear
FROM
[StockAX\Stock_step1.qvd]
(qvd)
WHERE 1=0;

Store QVDClearTable1 Into StockAX\Stock_step1.qvd(qvd);
drop Table QVDClearTable1;

FOR i=2 to $(StepKey)

Concatenate (Stock)
Load ItemInventDimIdKey,
Key,
Key as KeySt,
floor(№ДатыСклад) as №ДатыСклад,
     _Количество,
     _КоличествоИтогОстатки,
     _Себестоимость,
     _СебестоимостьИтогОстатки,
     '0' as FlagEx
FROM
[StockAX\Stock_step$(i).qvd]
(qvd);

Add NoConcatenate
QVDClearTable$(i):
Load *, '' as Clear
FROM
[StockAX\Stock_step$(i).qvd]
(qvd)
WHERE 1=0;

Store QVDClearTable$(i) Into StockAX\Stock_step$(i).qvd(qvd);
drop Table QVDClearTable$(i);

next i
// Гружу новые данные исключая из ранней ТФ уникальные Key
Concatenate
Load Key,
Key as KeySt1,
ItemInventDimIdKey,
floor(№ДатыСклад) as №ДатыСклад,
_Количество,
_КоличествоИтогОстатки,
_Себестоимость,
_СебестоимостьИтогОстатки,
'1' as FlagEx
FROM ОстаткиНаСкладахAX.qvd(qvd)
WHERE not Exists(KeySt,Key);

Drop Field KeySt, KeySt1;

Store Stock Into ОстаткиНаСкладахAX.qvd(qvd);
drop table Stock;

/*** Сохраняем дату модификации в QVD. Добавил в конец, потому что если в цикле ошибка, то дата не изменится и при повторной загрузке мы ничего не потеряем ***/
store ModifiedDateTable into StockAX\ModifiedDateTable.qvd(qvd);
drop table ModifiedDateTable;

END IF


Если будут пожелания по оптимизации - COOL!!!  :)

P.s.:
to Софья:
Если Вам необходимы остатки на конец года\Квартала\Месяца\Недели, то можно выгрузив данные, моим способом, на каждый день, делать с ними все что душе угодно)

Например по месячно:
Stock:
load * inline
[FlagSt
1
];
Inner join
load Key,
     №ДатыСклад,
     if(floor(№ДатыСклад) = floor(MonthEnd (№ДатыСклад)) ,1,0) as FlagSt,
     _КоличествоИтогОстатки,
     _СебестоимостьИтогОстатки
FROM ОстаткиНаСкладахAX.qvd(qvd);


В итоге можно иметь один документ Qlikview, создающий кучку QVD по остаткам и в зависимости от пожеланий пользователей/ настроек скрипта грузить ту или иную QVD.
В интерфейсе условие на выбор только одного значения по дате. Соответственно не будет пересчета, а будет вывод одной строки на Дату по уникальному Key со значением "_КоличествоИтогОстатки" и "_СебестоимостьИтогОстатки"

Софья

Добрый день всем.
Всё равно задача по уменьшению объёма данных осталась. Даже убирая все поля, оставляя связь только по продукту и подразделению, данные занимают много места, сократилось приложение всего на 500 МБ, итого - 3,5 ГБ один год.
Как раз поле, просчитывающее кол-во, всё и занимают - это KOL и KOL2. Остаток формируется как KOL-KOL2, так что обойти прогрузку этих критичных полей нельзя.
Если я уберу дробление по типам движения внутри прихода и расхода, я правильно понимаю, что количество корректно не будет отображаться по типам движения? 

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 = 14286904 AND T.AMOUNT >0 THEN T.AMOUNT
     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 - расходные документы


DmitryK, я больше понимаю синтаксис SQL, поэтому, признаться, мне многие вещи всё равно непонятны в QlikView, даже Ваше объяснение.
Я самоучка, поэтому ещё очень многого в логике QlikView не могу разобрать до сих пор. 

DmitryK

Сейчас попробую поподробнее описать)

DmitryK

Всем доброго времени суток.

Софья, у Вас есть задача указывать приход\расход?

А сейчас попробую разжевать свой код! :-)
(Рассмотрим первоначальную загрузку данных без PreLoad) Немного переделал код и убрал лишнее, чтобы передать в первую очередь мысль.
Начнём!)

1. Гружу все что есть (без группировок), но сортирую по ключу...в принципе не обязательно, но у меня без сортировки пару позиций считались не правильно. Если честно не вникал, времени не было :-)

Tmp_1:
Select  Concat(it.ItemId,'/',it.InventLocationId) as Key
,it.Date as №ДатаПроводки
,it.ItemId as %Продукт
,it.InventLocationId as %Подразделение
,it.QTY as _Количество,
,it.CostAmount as _Себестоимость
FROM INVENTTRANS as it
Order by Concat(Продукт,'/',Подразделение), it.Date;


Источник для работы у нас есть!) Самой простое выполнено)

2. У себя я группирую данные, по Дате и Ключу:


ADD NoConcatenate
Tmp:
Load AutoNumber(Key, 'Ctrl1') AS Key, //Генерируем Ключ. Ключ будет иметь значения 1,2,3,4,5 и т.д.
№ДатаПроводки,
%Продукт,
%Подразделение
sum(_Количество) as _Количество,
sum(_Себестоимость) as _Себестоимость
Resident Tmp_1
Group by №ДатаПроводки, %Продукт, %Подразделение, Key;


Сохраняем в QVD, дропаем:

store Tmp into StockTmp.qvd(qvd);
drop tables Tmp_1, Tmp;


3. Чтобы не нагружать сервер большим количеством данных, я обрабатываю данные по частям.
Считаем количество шагов для обработки по 1млн. уникальных значений Key:

TSK:
Load round(Count(DISTINCT Key)/1000000,1)+1 as StepKey
FROM StockTmp.qvd
(qvd);

Let StepKey = num(peek('StepKey',0,'TSK')); // Сохраняем количество шагов в переменную
LET g = 1; //Переменная для указания количества выгружаемых уникальных значений для обработки

drop Table TSK;


4. Выгружаем из QVD Всё что касаемо справочной инфо и сохраняем в QVD. Описывать не буду...и так понятно)))

5. Гружу из QVD данные для обработки. Т.е. Key, №ДатаПроводки, _Количество, _Себестоимость. Беру уникальные значения от 1 до 1000000. На след. шаге в цикле будут грузиться от 1000001 до 2000000:



FOR i=1 to $(StepKey)

T1:
NoConcatenate
Load Key,
№ДатаПроводки,
_Количество,
         _Себестоимость
FROM StockTmp.qvd(qvd)
WHERE   NUM(Key)>=$(g)
and NUM(Key)<$(g)+1000000;

LET g = $(g)+1000000;


6. Находим Тотал суммы, минимум и максимум по Дате. Тотал по сумме гружу, чтобы проверить актуальность загрузки. Т.е. если на сегодня Key1 равен 0, то смысл грузить его, т.к. он не продается с 2014 года. А если остаток есть, то грузим по Максимум. У меня реализовано на today().


T2:
Load Key,
min(№ДатаПроводки) as Min№ДатаПроводки,
IF(sum(_Количество) = 0, max(№ДатаПроводки), Today()) as Max№ДатаПроводки,
sum(_Количество) as _КоличествоTotal
Resident T1
Group by Key;


7. Далее проставляем пропущенные значения дат между min и max №ДатаОстатки (изначально джойнил, время обработки в разы больше было). Дропаем T2, проставляем к сгенерированным датам, значения Key.

T3:
Load Key,
Min№ДатаПроводки,
Max№ДатаПроводки,
num(Min№ДатаПроводки+IterNo()-1) as №ДатаПроводки2
resident T2
While (Min№ДатаПроводки+IterNo()-1)<=IF(_КоличествоTotal = 0, Max№ДатаПроводки, Today());

drop table T2;

join(T3)
Load FieldValue('Key',RecNo()) as Key
AutoGenerate FieldValueCount('Key');


Гружу повторно min И max значения по датам, а потом обрабатываю с помощью intervalmatch. Вдаваться в подробности не буду.

T6:
Load Key,
Min№ДатаПроводки,
IF(_КоличествоTotal = 0, Max№ДатаПроводки, Today()) as Max№ДатаПроводки;
Load Key,
min(№ДатаПроводки) as Min№ДатаПроводки,
max(№ДатаПроводки) as Max№ДатаПроводки,
sum(_Количество) as _КоличествоTotal
Resident T1
group by Key;

TRACE '// Проставляем даты';
inner join(T3)
IntervalMatch(№ДатаПроводки2,Key)
Load Key,
Min№ДатаПроводки,
Max№ДатаПроводки
Resident T6;

drop table T6;


Грузим наши Даты в ТФ, у нас она Т1:

join(T1)
Load Key,
№ДатаПроводки2 as №ДатаПроводки
Resident T3;


8. Заполняем значения Остатков на дату. Поля _Количество и _Себестоимость - для проверки, поля _КоличествоИтогОстатки и _СебестоимостьИтогОстатки - расчетные. Сортировка для красоты.

T4:
Load Key,
floor(№ДатаПроводки) as №ДатаПроводки,
num(IF(len(trim(_Количество))=0 and peek(Key)=Key,0, _Количество)) as _Количество,
num(IF(len(trim(_Количество))=0 and peek(Key)=Key, alt(peek('_КоличествоИтогОстатки'),0),
IF(peek(Key)=Key,_Количество + alt(peek('_КоличествоИтогОстатки'),0),_Количество))) as _КоличествоИтогОстатки,
num(IF(len(trim(_Себестоимость))=0 and peek(Key)=Key,0, _Себестоимость), '###0,00') as _Себестоимость,
num(IF(len(trim(_Себестоимость))=0 and peek(Key)=Key, alt(peek('_СебестоимостьИтогОстатки'),0),
    IF(peek(Key)=Key,_Себестоимость + alt(peek('_СебестоимостьИтогОстатки'),0),_Себестоимость)),'###0,00') as _СебестоимостьИтогОстатки
resident T1
order by Key,№ДатаПроводки;   


УДАЛЯЮ ЛИШНИЕ СТРОКИ с нулевыми остатками...а они нам нужны?) Лично мне нет, если необходимо видеть когда были последние списания, у меня есть табличка с выводом max ДатыПроводки.

T5:
NoConcatenate
Load *
Resident T4
WHERE _КоличествоИтогОстатки<>0;


Сохраняю в QVD, удаляю лишние таблицы и перехожу на след шаг, если необходимо.

drop table T4;

Store T5 Into StockAX\Stock_step$(i).qvd(qvd);

Drop table T1;
drop table T5;

next i

drop tables T1Tmp;



Вот и все...вроде не сложно...
Если будут вопросы, готов ответить)

Софья

DmitryK, у меня это основополагающая задача, и как раз подсчёт количества по типам движения в приходе и расходе и создаёт то колоссальное, по объёму, приложение.
Хотела спросить у Вас некоторые нюансы.
1) Вы пишите, что "грузите всё, что есть". Можно узнать, Вы грузите поля из какой-то БД? Хотелось бы знать, как можно прогрузить поля, не прописывая их класс/тип, как, допустим, у меня. Если я не укажу эти ограничения, то попросту у меня компьютер не прогрузит ничего, зависнет, потому что объём данных, мягко говоря, космический.
2) Так же обратила внимание, что у Вас нет в коде SQL перед Select. Я правильно понимаю, что если не указывать SQL, то после Select можно применять непосредственно синтаксис QlikView? Так же не увидела Load. Или Вы его просто не указали здесь, чтобы голову не забивать очевидными вещами?
2) У Вас часто встречаются такие операторы, как resident/drop/join. Можно о каждом из них кратко, буквально пару слов. Я читала о них в пособии, но сложилось неясное впечатление о необходимости их применения.

Извините, если сильно дотошная. Мне действительно хочется разобраться, а не написать код, к которому потом придётся сооружать костыли. Не с кем на работе проконсультироваться по QlikView, к сожалению, поэтому терроризирую вас всех здесь.

Яндекс.Метрика