Загрузка данных из

Автор DmitryK, 27 июня 2017, 09:42:50

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

DmitryK

P.s.: Отредактировано 27.06.2017 10:40.

Добрый день, Коллеги!
Прилетела ко мне вчера задачка. С виду просто, но оказалось что только с виду)

Файл не прилагаю, ибо на работе закрыт доступ на передачу файлов во вне. Уже пробовал, не получилось вставить. Попробую нарисовать так.

Загрузка производится в QLIKVIEW.

Задача: Имеется Excel с 1 листом, на котором расположены одной структуры таблицы. Сейчас попробую изобразить их:


A;B;C;D;E;F;G;H;I;J;K;L;M;N;O;P;Q;R;S;T;U;V;W;X;Y;Z;AA;AB
;;Январь;;Февраль;;Март;;Апрель;;Май;;Июнь;;Июль;;Август;;Сентябрь;;Октябрь;;Ноябрь;;Декабрь;;2016;
Клиент;Продукт;Кол;Сум;Кол;Сум;Кол;Сум;Кол;Сум;Кол;Сум;Кол;Сум;Кол;Сум;Кол;Сум;Кол;Сум;Кол;Сум;Кол;Сум;Кол;Сум;Кол;Сум
ИП Иванов;Помидоры;100;150;200;250;300;350;400;450;500;550;600;650;700;750;800;850;900;950;1000;1050;1100;1150;1200;1250;7800;8400
ООО Сигма;Бананы;10;15;20;25;30;35;40;45;50;55;60;65;70;75;80;85;90;95;100;105;110;115;120;125;780;840
;;;;;;;;;;;;;;;;;;;;;;;;;;;
;;Январь;;Февраль;;Март;;Апрель;;Май;;Июнь;;Июль;;Август;;Сентябрь;;Октябрь;;Ноябрь;;Декабрь;;2017;
Клиент;Продукт;Кол;Сум;Кол;Сум;Кол;Сум;Кол;Сум;Кол;Сум;Кол;Сум;Кол;Сум;Кол;Сум;Кол;Сум;Кол;Сум;Кол;Сум;Кол;Сум;Кол;Сум
ИП Иванов;Помидоры;200;300;400;500;600;700;800;900;1000;1100;1200;1300;1400;1500;1600;1700;1800;1900;2000;2100;2200;2300;2400;2500;15600;16800
ООО Сигма;Бананы;20;30;40;50;60;70;80;90;100;110;120;130;140;150;160;170;180;190;200;210;220;230;240;250;1560;1680


Если скопировать в Excel, то вставляется нормально. Но надо разделить данные по колонкам, выделив столбец, далее вкладка Данные, выбираем Текст по столбцам и разделитель выбрать ';'.

Имеем:
- Первая строка - заголовки, наименования столбцов в Excel
- На каждый месяц 2 показателя (Количество и сумма).
- Каждая таблица - один год.
- В каждой таблице разное количество строк и наименования продуктов, как и клиентов могут не совпадать.
- Таких таблиц на листе > 20.
- Итоги по году, не нужны. Необходимо только значение года.

Необходимо объединить эти таблицы в одну с разбивкой по годам. Месяца обработать кросс таблицей и разбить их по показателям, т.е. получить вот что (На примере 16 года по помидорам):


Клиент   Продукт   Год   Месяц   Количество   Сумма
ИП Иванов   Помидоры   2015   Январь   100   150
ИП Иванов   Помидоры   2015   Февраль   200   250
ИП Иванов   Помидоры   2015   Март   300   350
ИП Иванов   Помидоры   2015   Апрель   400   450
ИП Иванов   Помидоры   2015   Май   500   550
ИП Иванов   Помидоры   2015   Июнь   600   650
ИП Иванов   Помидоры   2015   Июль   700   750
ИП Иванов   Помидоры   2015   Август   800   850
ИП Иванов   Помидоры   2015   Сентябрь   900   950
ИП Иванов   Помидоры   2015   Октябрь   1000   1050
ИП Иванов   Помидоры   2015   Ноябрь   1100   1150
ИП Иванов   Помидоры   2015   Декабрь   1200   1250



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

Заранее спасибо за помощь!

admin

Привет.
Делал подобное.
Вечером поищу  :)

Multicvet

Как быстрое решение - можно ли нормализовать Excel? например, добавив столбец Год по формуле - https://yadi.sk/i/io1E_Fq83KWZuD.
Решение деревенское, но как вариант.

DmitryK

#3
Таких файлов безумное количество и проставлять вручную Год...не вариант.
Архив не могу посмотреть, только вечером. Скачивание на работе тоже под запретом)

Пока что сделано вот что:
1. Проставлен флаг там, где я вижу "январь". Далее накапливаем каждый раз, когда видим флаг.
2. Поле Год перенес в начало.

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

Tmp:
LOAD numsum(Flag, peek( 'Table' ) ) as Table
,AB,Flag,A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,AA
;
LOAD if(C = 'Январь', 1,) as Flag,
A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,AA,AB
FROM
[Форум QlikView.xlsx]
(ooxml, no labels, table is Лист1);


Осталось разобраться с 2мя уровнями измерений. А именно с группировкой Месяц -> Количество\Сумма

Чуть позже добавлю полный код)

admin

#4
Привет
Посмотри код:

// получаем сырой разворот
t1:
CrossTable(Поле, Данные, 3)
LOAD RowNo() as IdRow,
@1,
     @2,
     @3,
     @4,
     @5,
     @6,
     @7,
     @8,
     @9,
     @10,
     @11,
     @12,
     @13,
     @14,
     @15,
     @16,
     @17,
     @18,
     @19,
     @20,
     @21,
     @22,
     @23,
     @24,
     @25,
     @26,
     @27,
     @28
FROM [ТестФорум.xls] (biff, no labels, table is Лист1$);

RENAME Field @1 to Клиент;
RENAME Field @2 to Продукт;

// Формируем поле Месяца
t2:
load Distinct Поле Resident t1
Order by Поле;
left join (t2)
load Поле, Данные As Месяц
resident t1
Where IdRow=1
Order by Поле;

t3:
NoConcatenate
load Поле, if(len(Месяц)>0,Месяц,peek(Месяц)) as Месяц
Resident t2
order by Поле;

DROP Table t2;

// Формируем поле Год
t4:
load only(Данные) as Год Resident t1 where  WildMatch(Данные,'201*') Group by Данные;
let vYear=Peek('Год',0,'t4');
DROP Table t4;

join (t3) load  Distinct $(vYear) as Год Resident t3 ;
// Формируем поле Показатель
t5:
load Поле, Данные as Показатель Resident t1 where IdRow=2;

// Промежуточный результат
t11:
NoConcatenate load * Resident t1 where IdRow>2;
drop Table t1;
Left join (t11) load * Resident t5;
drop Table t5;
Left join (t11) load * Resident t3 Where not  WildMatch(Месяц,'201*') ;
drop Table t3;


Основная идея состоит в использовании номера колонки как ключа.

DmitryK

Фух, наконец-то вернулся к задаче!

Admin, спасибо большое! Все получилось!

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