Страницы: 1
RSS
Power Query. Объединение таблиц с множеством условий, Power Query. Объединение таблиц с множеством условий
 
Добрый день!

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

Описание.
Есть 2 таблицы:
-Таблица Лист1 - данные о необходимых материалах с кодом и применяемостью и периодом ограничения по датам приходов;
-Таблица Лист2 - данные о пришедших материалах, дата прихода, количество, цена и обоснование. Таблицу Лист1, нужно объединить с таблицей Лист2. Таблица Лист1 - главная.

Цель.
Чтобы при объединении таблиц Лист1 (к которой подцепляю) и Лист2 (которую подцепляю) можно было добавить условия:
1)Дата Лист2 вписывалась в отрезок Период начала-Период конца;
2)Так же, чтобы Количество Лист2 меньше или равно Применяемости Лист1;
3)Исходя из вышеперечисленных условий считалась средняя цена из Лист2;
4)И выводились все причастные обоснования из Лист2 (типа Сцепить "Обоснование, Обоснование");
Т.е. условия 1) и 2) выполнялись одновременно.
Примерно что хочется получить в Листе "Примерный итог".

Что делаю.
Моих знаний хватает на то, чтобы сформировать из каждой таблицы Лист1 и Лист2 запросы, далее объединить их и... всё.. (Лист3). Вообще понимания нет возможно ли решить такое через PQ. Аналогов не нашел особо.

Пример приложил.

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

P.S. Если гиблое дело пытаться так решить прошу тоже написать об этом!
 
Цитата
delph3r написал:
гиблое дело
нет, все делается легко. Только вопросики есть:
1. Материалы должны совпадать по коду или по наименованию?
2. Что делать, если строке таблицы 1 соответствуют несколько строк таблицы 2?
3. Что делать, если нескольким строкам таблицы 1 соответствует одна строка таблицы 2? Строки из 2 нужно использовать однократно?
4. И что означает "считалась средняя цена"?
p.s. из описания, вроде бы, следует, что прицеплять надо показатели нескольких строк таблицы 2 (средняя, сцеплять что-то...), но пример этого не показывает и про дату, например, тоже ничего не говорится - их же тоже несколько будет. Внесите ясность.
Изменено: AlienSx - 22.05.2024 09:30:15
Пришелец-прораб.
 
Цитата
написал:
нет, все делается легко. Только вопросики есть: 1. Материалы должны совпадать по коду или по наименованию?2. Что делать, если строке таблицы 1 соответствуют несколько строк таблицы 2?3. Что делать, если нескольким строкам таблицы 1 соответствует одна строка таблицы 2? Строки из 2 нужно использовать однократно?4. И что означает "считалась средняя цена"?p.s. из описания, вроде бы, следует, что прицеплять надо показатели нескольких строк таблицы 2 (средняя, сцеплять что-то...), но пример этого не показывает и про дату, например, тоже ничего не говорится - их же тоже несколько будет. Внесите ясность.
Согласен, что не целиком описал все нюансы. Попытаюсь дополнить, чтобы более полно раскрыть.

Цитата
написал:
1. Материалы должны совпадать по коду или по наименованию?
Материалы должны совпадать по коду.

Цитата
написал:
2. Что делать, если строке таблицы 1 соответствуют несколько строк таблицы 2?
Если строке таблицы 1 соответствует несколько строк таблицы 2 (все условия выполняются при этом), то тогда необходимо вывести среднее значение цены, вывести максимальную дату, суммировать количество, и сцепить в одну строку обоснование. Но это все равно должно быть не больше, чем применяемость.
Цитата
написал:
3. Что делать, если нескольким строкам таблицы 1 соответствует одна строка таблицы 2? Строки из 2 нужно использовать однократно?
Такой вариант исключен в моем случае. Т.е. в таблице 1 находятся уникальные (единоразово повторяющиеся) коды.
Цитата
написал:
4. И что означает "считалась средняя цена"?
Аналог СРЗНАЧ в Excel из приходов таблицы 2, которые соответствуют условиям.


P.S.
По-другому получается как будто данные в таблице 2 группируются при объединении двух запросов в PQ (скриншот приложил) и если в этой группе данные подходят под условия Таблицы 2, то их надо "усреднить" (вывести среднее значение цены, вывести максимальную дату, суммировать количество, и сцепить в одну строку обоснование).


Но сейчас понимаю, что получается тупиковый вариант в ситуации, когда в Таблице 2 есть один приход с количеством большим, чем применяемость в Таблице 1, то по сути по моим условиям это ошибка сразу.


Как это побороть? Если только вручную? Ведь по сути если пришло больше, чем требуется, то использовать можно, но просто останется остаток. Или можно как-то учесть в коде, чтобы такие варианты учитывались, типа если не соответствует ни один приход по количеству, то брать один приход с максимальной ценой. Но это я уже так "мудрю". Поправьте, если что.

Спасибо, что откликнулись!
Изменено: delph3r - 22.05.2024 11:09:06
 
AlienSx,

Когда-то была задача из группы с одинаковыми кодом, но разными датами найти максимально актуальную дату. Пробовал работать по этой теме. Тут я понял, что идет как раз группировка по условию "№" и потом уже из этой группы выводится максимальная Дата, и в целом получилось отработать по такому примеру. Но как это модифицировать, к моей текущей задаче не знаю. Но это так, уже лирика.
Изменено: delph3r - 22.05.2024 11:28:25
 
о, тепленькая пошла...
Давайте обобщим: у вас есть "потребности" (табл 1) и есть то, что "поступило" (табл 2). Задача - потратить то, что поступило, чтобы максимально удовлетворить каждую "потребность" и посчитать при этом среднюю стоимость цену (не СРЗНАЧ, кстати, а надо исходя из использования конкретных поступлений в рамках конкретных объемов их использования), сцепить значения в столбце, посчитать макс дату, суммировать кол-во. При этом поступления можно использовать частично и на разные "потребности". Все верно?
Если не заморачиваться оптимизациями наполняемости "рюкзаков", а задаться очередностью расхода поступлений (FIFO или LIFO), то задача вполне себе решаемая, пусть и не так просто, как казалось вначале. Надо "мутить" итерации (рекурсия, List.Generate) с одновременным обновлением "остатков на складе" из числа поступлений. Если здесь не дождетесь у моря погоды решения, то обратитесь в Работу. А пока посоветовал бы почитать темы форума про FIFO.
p.s. И, кстати, раз уж вы так легко меняете условия использования "поступлений", то условие ограничения по периоду дат ("между") уже выглядит не очень - может быть надо ограничивать поступления сверху, т.е. "не позднее". Но это только вам известно...
Изменено: AlienSx - 23.05.2024 09:34:29 (ср цена, а не стоимость)
Пришелец-прораб.
 
AlienSx,

Ну т.е. мысль о том, что можно сначала группировать значения по коду, а потом их уже внутри как-то "фильтровать" кодом (т.е. ограничивать выборку по условию даты) и потом суммировать Количество нарастающим итогом, пока это не превысит Потребность и в итоге вывести среднее значение цены, вывести максимальную дату, вывести суммарное количество, и сцепить в одну строку обоснование.

Т.е. работать вот с этими данными как-то можно? Это же уже отсортированные данные.


Это не правильный ход мыслей?
Изменено: delph3r - 22.05.2024 13:53:03
 
Цитата
написал:
p.s. И, кстати, раз уж вы так легко меняете условия использования "поступлений", то условие ограничения по периоду дат ("между") уже выглядит не очень - может быть надо ограничивать поступления сверху, т.е. "не позднее". Но это только вам известно...
Это я просто для примера сделал, чтобы как-то понятнее объяснить. По факту естественно данные являются константой по цене, дате и тд. Просто я не могу их представить здесь в первичном виде, поэтому приходится "выдумывать" пример.
Изменено: delph3r - 22.05.2024 13:51:33
 
delph3r, известно ли максимальное предельное кол-во строк в правой таблице (таблица Лист2), которые могут соответствовать значениям левой таблицы (табл Лист1) если делать левый join? - т.е. например "в левую таблицу для каждого значения никогда не подтянется больше 10 значений из правой"
 
voler83, думаю тут ТС просто должен разместить тему (перенести текущую) в платном разделе.
AlienSx реализует :)
 
surkenny, думаю, надо новое требование создать, чтобы ТС описывали исчерпывающие условия задач )) - за последний месяц это как минимум третья тема (были также с задачей про геологические пробы и оч еще похожая), в которой ТС не могут определиться с исходными условиями, которые могут знать только они.
Изменено: voler83 - 22.05.2024 21:18:20
 
Цитата
surkenny написал:
AlienSx  реализует
:) Не, я в платном безобразии не участвую :) Гусары денег не берут!  :D
сущая монстра
Пришелец-прораб.
 
Цитата
написал:
известно ли максимальное предельное кол-во строк в правой таблице (таблица Лист2), которые могут соответствовать значениям левой таблицы (табл Лист1) если делать левый join? - т.е. например "в левую таблицу для каждого значения никогда не подтянется больше 10 значений из правой"
Не знал о такой особенности в PQ при объединении запросов. Что имеется ввиду
Цитата
написал:
в левую таблицу для каждого значения никогда не подтянется больше 10 значений из правой"
? Как это ошибка должна выглядеть?

В исходной таблице при объединении запросов у меня подцепляются более 60 строк по Коду из Таблицы2 к Таблице1. Т.е. создается колонка со значениями Table, при их раскрытии там более 60 строк наименований совпадающих по Коду.
 
delph3r, нет там особенностей)) просто если мало совпадений в двух таблицах заведомо, то можно "руками" через if-then-else быстро бы было сделать через некоторую конструкцию....но уже не актуально
 
Цитата
написал:
сущая монстра
Благодарю за уделенное время и помощь! Вернусь, когда протестирую и попытаюсь запустить и разобраться с кодом. Хотя мой уровень конечно не подходит для чтения таких кодов, но все таки.
 
delph3r, я сделал все кроме даты - что делать с датой поступления, если их будет несколько? перечислить все даты через запятую в одной ячейке  как и "обоснование"? или указать только максимальную или минимальную?
Изменено: voler83 - 23.05.2024 15:11:12
 
Цитата
voler83 написал:
что делать с датой поступления, если их будет несколько?
Цитата
delph3r написал:
Если строке таблицы 1 соответствует несколько строк таблицы 2 (все условия выполняются при этом), то тогда необходимо вывести среднее значение цены, вывести максимальную дату, суммировать количество, и сцепить в одну строку обоснование. Но это все равно должно быть не больше, чем применяемость.
Пришелец-прораб.
 
delph3r, возник еще большой вопрос, вернее, его уже задали, но ответ не получен, а он принципиально важен (возможно, не вижу между строк ответа) - если в правой таблице с фактическими поступлением есть какое-либо значение кол-ва, которое поступило не первое (fifo) и не последнее (lifo), а в середине периода совместно с др. поступлениями, и при этом только указанное поступление по кол-ву подходит под применимость (т.е. допустим все остальные значения изначально больше применимости), то это поступление в периоде надо оставлять? т.е. по сути вопрос в следующем - надо ли всегда сравнивать применимость с абсолютно каждым значением кол-ва? и если надо и при этом несколько значений в сумме <= применимости, то какие их них суммировать? - по дате поступления (опять же fifo/lifo) или по иному принципу?
а если не надо с каждым значением кол-ва сравнивать, то надо смотреть только крайние поступления или перебирать пока не натолкнется на первое подходящее значение?
у вас пример рафинированно ограничен, в нем нет никакой вариативности, из примера нельзя сделать никаких обобщающих выводов
если необходим перебор всех значений с заданием условия какое из них оставлять, то это принципиально усложняет задачу, если это не fifo или lifo, а какой-то др. способ отбора значений.

upd: прикрепил свой вариант
поскольку принципиальные условия задачи не заданы, то:
- у меня List.Generate оч. ограничен, т.к. берет кол-во по методу fifo, но если первое же значение кол-ва будет больше применимости, то перебор остановится. Дальше допиливать не стал, т.к. без условий бессмысленно.
- дата_end берется максимальная из дат поступления, т.к. (см. предыдущий пункт) не ясно как допиливать.

Среднюю цену считал путем накопления через List.Generate внутри записи в двух полях отдельно значений (цена Х кол-во) и (кол-во) по каждому поступлению, потом одно накопленное делил на другое накопленное.

upd2: существенное упрощение для любого условия - необходимо сразу фильтром убрать все поступления, которые >применяемости. в моем коде это сразу исключит недостаток условия продолжения цикла в List.Generate, про который написал. Изм-й в код не вносил - после join'а необходимо поставить условие сравнения с применяемостью и фильтрануть.
Изменено: voler83 - 24.05.2024 11:03:48
 
voler83, прошу прощения, что не отвечал, т.к. не имел доступ к уч. записи до сегодняшнего дня.
 
Цитата
написал:
у вас пример рафинированно ограничен
Абсолютно согласен.

Цитата
написал:
т.е. по сути вопрос в следующем - надо ли всегда сравнивать применимость с абсолютно каждым значением кол-ва? и если надо и при этом несколько значений в сумме <= применимости, то какие их них суммировать? - по дате поступления (опять же fifo/lifo) или по иному принципу?
Думаю, что необходимо всегда сравнивать применимость с абсолютно каждым значением количества. По дате поступления - fifo.

Цитата
написал:
upd: прикрепил свой вариант
Большое спасибо!

Цитата
написал:
upd2: существенное упрощение для любого условия - необходимо сразу фильтром убрать все поступления, которые >применяемости. в моем коде это сразу исключит недостаток условия продолжения цикла в List.Generate, про который написал. Изм-й в код не вносил - после join'а необходимо поставить условие сравнения с применяемостью и фильтрануть.
Имеете ввиду добавить Пользовательский столбец с условием [Количество]>[Применяемости]?
 
Цитата
delph3r написал:
Имеете ввиду добавить Пользовательский столбец с условием [Количество]>[Применяемости]?
да. Исходя из ваших вводных, мне кажется, это правильное условие - такое значение никогда не попадет в итоговую таблицу (??). Но это вам решать.
Изменено: voler83 - 27.05.2024 08:38:39
 
Цитата
delph3r написал:
Думаю, что необходимо всегда сравнивать применимость с абсолютно каждым значением количества. По дате поступления - fifo.
если поставите предварительное условие  [Количество]>[Применяемости], то у меня List.Generate должно правильно работать (также см. на правильность сортировки перед List.Generate - я по возрастанию дат поставил = fifo).
Но надо макс. дату поправить - если захотите разобраться, то там по аналогии все можно сделать как др. аргументы в List.Generate.
п.с.: возможно, у меня в List.Generate слишком много аргументов, и на больших данных наверно будет тормозить. Я это упражнение сделал, чтобы прокачать List.Generate для себя.

upd: если будете использовать мой вариант, удалите у меня в файле запрос "Таблица 1" - он не используется, забыл удалить.
Изменено: voler83 - 27.05.2024 09:00:24
Страницы: 1
Наверх