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
Посоветуйте в какую сторону копать, пожалуйста. Возможно есть уже готовые решения, чтобы не изобретать велосипед.
Заранее спасибо за помощь!
Привет.
Делал подобное.
Вечером поищу :)
Как быстрое решение - можно ли нормализовать Excel? например, добавив столбец Год по формуле - https://yadi.sk/i/io1E_Fq83KWZuD (https://yadi.sk/i/io1E_Fq83KWZuD).
Решение деревенское, но как вариант.
Таких файлов безумное количество и проставлять вручную Год...не вариант.
Архив не могу посмотреть, только вечером. Скачивание на работе тоже под запретом)
Пока что сделано вот что:
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мя уровнями измерений. А именно с группировкой Месяц -> Количество\Сумма
Чуть позже добавлю полный код)
Привет
Посмотри код:
// получаем сырой разворот
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;
Основная идея состоит в использовании номера колонки как ключа.
Фух, наконец-то вернулся к задаче!
Admin, спасибо большое! Все получилось!