Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
PQ: сопоставление и дополнение строк одной таблицы из другой таблицы-справочника
 
#1
25.06.2021 15:08:27
Доброго дня, коллеги! У меня есть выгрузки из 1С с еженедельными остатками по товарам. Кто тесно работает с 1С, знает, что есть отчеты, которые не будут выводить результат, если не было движения за выбранный период. Т.е. формируя отчет за 10 недель я ожидаю получить 10 строк данных с остатками, но если не было движения, то будет только первая, последняя неделя + те недели, по которым были изменения остатков. Ну вот, проблему описал. Я пытаюсь восполнить недостающие недели с помощью PQ. Я попробовал реализовать следующий план: 1. объединил справочник номенклатуры и справочник недель декартовым произведением. 2. полученный результат "заджойнил" с таблицей фактов и вытащил остатки. Вроде бы успех, но на боевой модели все это крашится, потому что таблица фактов = 2 млн. строк, а созданная справочная таблица декартовым произведением вышла под 5 млн. Вообщем план не удался, а других у меня нет. Может кто знает как решить подобную задачу? Во вложении образец исходных данных и конечного результата.
 
 
 
#2
25.06.2021 15:46:17
Добрый день!
Я делаю подобные отчеты в 1с, только с интервалом в месяц.
В 1с я решаю эту проблему путем добавления в поля периода. Либо выгружаю данные за каждый месяц отдельно.
 
 
 
#3
25.06.2021 16:45:39
Пока могу предложить такой вариант. Но он дублирует значения по кол-ву. Если нужно чтобы не дублировал, то можете использовать мое вчерашнее решение и скрестить с этим. У меня сейчас времени нет, бежать надо. Если уж не получится, то до понедельника.
Код
let
    minweek = 1,
    maxweek = 15,
    fx = (t) =>
    let Sort = Table.Buffer( Table.Sort( t,{{"неделя", Order.Ascending}}) ),
        Custom1 = Table.FromColumns( 
            Table.ToColumns(Sort) & { {minweek-1} & List.RemoveLastN( Sort[неделя], 1 ), List.RemoveLastN( Sort[неделя], 1 ) & {maxweek}}, 
            Table.ColumnNames(WeekNum) & {"пред_неделя", "нед"} ),
        MergedColumns = Table.CombineColumns( Table.RemoveColumns(Custom1,{"неделя"}), {"пред_неделя", "нед"},(x)=>{x{0}+1..x{1}},"неделя"),
        Expanded = Table.ExpandListColumn(MergedColumns, "неделя")
    in Expanded,

    Source = Excel.CurrentWorkbook(){[Name="Факт"]}[Content],
    WeekNum = Table.TransformColumns(Source, {{"неделя", each Int64.From(Text.AfterDelimiter(_, " ")), Int64.Type}}),
    #"Grouped Rows" = Table.Group(WeekNum, {"код"}, {{"tab", fx, type table [код=number, неделя=number, #"кол-во"=number]}}),
    tab = Table.Combine( #"Grouped Rows"[tab] )
in
    tab
Вот горшок пустой, он предмет простой...
 
 
 
#4
25.06.2021 16:52:47
Приветствую
Еще вариант
Код
let
    a=Table.Buffer( Table.FromColumns({List.Transform( {1..15} ,each "неделя " & Text.From(_))}) ),
    Source = Excel.CurrentWorkbook(){[Name="Факт"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"код"}, {{"b", each 
                        let    
                          c = Table.Join(a, {"Column1"}, _, {"неделя"}, JoinKind.LeftOuter),
                          b = Table.FillUp(c,{"код"})
                        in Table.FillDown(b,{"код"})       }}),
    Custom1 = Table.Combine(#"Grouped Rows"[b])
in
    Custom1
Сколько по времени отработает - не знаю.  
НА МЯСО В ПРОФИЛЕ
 
 
 
#5
25.06.2021 17:18:05
Цитата
Михаил Л написал:
Сколько по времени отработает - не знаю
В принципе нормально бегает
Осталось изменить под необходимое. Ненужное удалить
НА МЯСО В ПРОФИЛЕ
 
 
 
#6
29.06.2021 08:03:16
Отвечаю с задержкой: пока удалось наложить на боевую модель в PBI код Михаил Л, (он показался проще, с него и начал). В результате исходная таблица в 2 млн. строк преобразовалась в 4 млн. На таком объеме данных запрос отработал за 40 мин. со 100% загрузкой ЦП и оперативки, ну и диск так же под 100% ушел (в это время ничего не мог более делать). Проц у меня: Itel Core i5-7500 3,40 Ghz, оперативки - 16 гб. Продолжаю тестирование (мне еще нужно будет добавить Filldown с учетом кода номенклатуры)
 
 
 
#7
29.06.2021 08:38:43
Vladimir Chebykin,
Добрый день. А что такое КОД в таблице? каков смысл этого столбика?
Чем отличается
код 1 неделя 2
от
код 2 неделя 2
Код генерируется таблицей фактов изначально?
Это заданное значение?
 
 
 
#8
29.06.2021 08:53:42
код1 и код2 - это два разных товара. Расчеты нужно вести по каждой отдельной номенклатурной позиции. Используется как ключ для связи с номенклатурным справочником.
 
 
 
#9
29.06.2021 08:55:41
Vladimir Chebykin,
Код генерирует 1с в рамках каких-то правил, или он может быть сегодня 000000123 а завтра ПБ000001224?
Вам это все нужно в рамках PBi или экселя?
Почему сводными таблицами не пользуетесь?

У  меня схожие бывают дела. Я создал несколько справочников:
Справочник ТМЦ, Справочник ДАТ (календарь, в том числе недели)
И дальше разрулил мерами.
Делать из 2х млн строк, 4млн строк - глупо, если помните мои старые посты, я тоже этим увлекался.
Однако с радостью обнаружил что DAX с мерами в десятки раз быстрее PQ с его тормозами.
Но если ограничения есть, на формат вывода данных, тогда конечно понимаю Вас.
Изменено: lostandleft - 29.06.2021 08:58:32
 
 
 
#10
29.06.2021 09:17:54
lostandleft, не пугайте военных войной, а кота сосиской. Уж в чем Владимир шарит, так это в PBI, связях и мерах, вот с PQ у него пробел имеется, это да.
Вот горшок пустой, он предмет простой...
 
 
 
#11
29.06.2021 09:38:38
Цитата
lostandleft написал:
Код генерирует 1с в рамках каких-то правил, или он может быть сегодня 000000123 а завтра ПБ000001224?
какое отношение имеет данный вопрос к этой теме?
Цитата
lostandleft написал:
Почему сводными таблицами не пользуетесь?
на основании чего Вы так решили?
Цитата
lostandleft написал:
Делать из 2х млн строк, 4млн строк - глупо
, эти 2 млн., все равно нужно превратить в 4 млн, чтобы корректно посчитать оборачиваемость остатков (на самом деле их фактически и есть 4 млн, только 1С из-за логики внутренних расчетов выводит только 2 млн (перечитайте внимательно 1-ое сообщение). lostandleft, я смотрю Вы уже считаете, что поднатаскались в DAX, тогда вопрос: как быстро или долго будут обрабатывать данные меры по двум сценариям:
1-ый сценарий: один раз сделать расчет в PQ, и высчитать фактические остатки на каждый день/неделю и потом по ним считать оборачиваемость мерами?
2-ой сценарий: гоним данные из 1С как есть, т.е. те самые 2 млн. строк, потом создаем мерами виртуальные таблицы, с теми самыми 4 млн. трок и уже их загоням в расчет оборачиваемости?
Попробуйте самостоятельно посчитать плюсы и минусы каждого подхода.
Цитата
lostandleft написал:
если помните мои старые посты, я тоже этим увлекался.
прекрасно помню, но у Вас там проблемы были связаны в первую очередь с правильностью составления модели данных, а не логикой расчетов.
 
 
 
#12
29.06.2021 09:53:33
Vladimir Chebykin, а если сделать ход конем? Отдельный справочник номенклатур имеется? Что если просто каждую недельную выгрузку обогащать недостающими кодами прикручивая их снизу и удаляя дубликаты, а потом уже собирать данные из каждого файла в общий, так по идее быстрее должно получиться. Но тогда в файл добавятся номенклатуры, по которым вообще движения не было за исследуемый период. Это приемлемо?
Вот горшок пустой, он предмет простой...
 
 
 
#13
29.06.2021 10:19:24
PooHkrd, не, немного не так. Если бы я делал выгрузку каждой недели отдельно, то данного топика вообще бы не было, потому что остатки без движения (приход и расход) в таком случае отображаются, я же делаю скопом по 6-8 недель, и тогда возникает проблема данного поста. Т.е. представим, что за 8 недель не было ни одной продажи и остатки не менялись, в таком случае выгрузка за 8 недель покажет остатки на 1 и 8 неделю (как начало и конец выбранного периода), а с 2-7 данных не будет вообще, т.е. данные строки отсутствуют и вместо 8 строк отчет 1С отобразит только 2 строки.
Вот и получается, что обогатить нужно именно недостающие периоды по каждому коду номенклатуры.
Изменено: Vladimir Chebykin - 29.06.2021 10:20:01
 
 
 
#14
29.06.2021 10:39:46
Vladimir Chebykin, тогда обогащать нужно на этапе до сборки файлов, это будет значительно быстрее.
Вот горшок пустой, он предмет простой...
 
 
 
#15
29.06.2021 11:11:47
Цитата
Vladimir Chebykin написал:
эти 2 млн., все равно нужно превратить в 4 млн, чтобы корректно посчитать оборачиваемость остатков
https://www.planetaexcel.ru/forum/index.php?PAGE_NAME=message&FID=1&TID=129574&a...

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

Цитата
Vladimir Chebykin написал:
какое отношение имеет данный вопрос к этой теме?
Я подумал, что если справочник Вы выгрузить не можете, то можно было бы сгенерировать коды, но уже понял, что это неприменимо, проще выгрузить справочник из 1с.

Цитата
Vladimir Chebykin написал:
как быстро или долго будут обрабатывать данные меры по двум сценариям:
Не знаю, нужно пробовать на конкретных данных.

Участвовать дальше, в обсуждении желание пропало.
 
 
 
#16
29.06.2021 11:32:39
Цитата
PooHkrd написал:
тогда обогащать нужно на этапе до сборки файлов, это будет значительно быстрее.
можно методику кратко описать?
 
 
 
#17
29.06.2021 11:57:38
Vladimir Chebykin, когда делаете запрос к папке и разворачиваете структуру бинарников вот таким образом:

То автоматически генерится вот такая структура из запросов:

где
123 - это собственно запрос к папке с названием "123"
Запрос "Пример файла" - это запрос, который по заданному в нем алгоритму выбирает тот единственный файл, из папки по образцу которого будет формироваться шаблонный запрос обработки и функция на его основе.
Запарос "Параметр1" - это собственно параметр, значением которого является бинарное содержимое файла из запроса "Пример файла"
Запрос "Преобразовать пример файла" - это шаблонный запрос, в который в качестве параметра передается бинарник из предыдущего шага и в нем же формируется алгоритм обработки единичного файла из папки перед сборкой в единый массив
Функция "Преобразовать файл" - это функция, которая связана с запросом "Преобразовать пример файла", Что это значит? Это значит что код функции берется напрямую из этого запроса, т.е. если изменить запрос-шаблон, то автоматически изменится и функция. Эта самая функция как раз и вызывается для обработки бинарников в запросе "123".
Что из вышеизложенного следует? Лезем в этот самый запрос-шаблон (он же "Преобразовать пример файла") и в нем производим обогащение по выбранному вами алгоритму, а далее, когда вы будете разворачивать содержимое файлов в единый массив, там уже будут все нужные вам строки. Такая обработка всегда работает гораздо быстрее.
Еще позволю себе наглость рекомендовать выгрузку отчетов из 1С в csv, это также заметно ускорит обработку.
Вот горшок пустой, он предмет простой...
 
 
 
#18
29.06.2021 12:23:51
PooHkrd, большое спасибо - я въехал в методу. Буду пробовать.
 
 
 
Страницы: 1
Наверх