Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
PQ. Интервальный поиск. Объединение больших таблиц со сложным условием
 
#1
23.11.2021 17:12:17
Добрый день.
На просторах интернета нашел пример PQ, который "подтягивает" из одной таблицы  в другую данные, действующие на определенную дату.

Во вложении пример, где
Таблица "Sale" содержит:
код товара, Регион, Дату реализации.
Таблица "Ак" содержит
код товара, регион, категорию, дату начал действия категории.
Запрос PQ, который в таблицу "Sale" добавляет "категорию", действующую на дату продажи.

Приложенный "Запрос PQ" выполняет поставленную задачу, но только на не больших массивах данных.
На "рабочей"  базе, где таблица "Ак" содержит несколько млн строк. PQ "выжирает всю оперативку" и компьютер зависает.

Помогите оптимизировать "Запрос PQ"
Спасибо
 
 
 
#2
23.11.2021 17:28:30
vikttur, Power Query. Интервальный поиск.
Neostt, проверяйте рыжую табличку.
Вот горшок пустой, он предмет простой...
 
 
 
#3
23.11.2021 17:41:40
Запрос отработал с ошибкой
 
 
 
#4
23.11.2021 17:55:42
Neostt, ну так сортировку по коду добавьте. Вы главное в смысл алгоритма въехали?
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Sale"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Дата реализации", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "тип", each 1),
    #"Duplicated Column" = Table.DuplicateColumn(#"Added Custom", "Дата реализации", "Дата нач действия"),
    Custom1 = #"Duplicated Column" & AK,
    #"Sorted Rows" = Table.Sort(Custom1,{{"Код", Order.Ascending},{"Регион", Order.Ascending}, {"Дата нач действия", Order.Ascending}, {"тип", Order.Descending}}),
    #"Filled Down" = Table.FillDown(#"Sorted Rows",{"Категория"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([тип] = 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"тип", "Дата нач действия"})
in
    #"Removed Columns"
Вот горшок пустой, он предмет простой...
 
 
 
#5
23.11.2021 17:57:09
Понял, Спасибо
В вашем примере внес изменения в сортировку, все получилось
 
 
 
#6
23.11.2021 22:06:25
Neostt, обратите внимание, что схема рабочая, только в случае наличия в справочнике АК всех сочетаний кода и региона с датой более ранней чем самая ранняя дата в таблице Sales. Если такой даты не будет, то туда съедет категория с более верхней по ходу сортировки группы.
Впрочем и в вашем изначальном варианте в этом случае будет выведена ошибка. Здесь я сохранял закладочку на усложненный вариант данного алгоритма, если интересно можете ознакомиться.
Вот горшок пустой, он предмет простой...
 
 
 
#7
24.11.2021 09:28:52
Цитата
написал:
Neostt , обратите внимание, что схема рабочая, только в случае наличия в справочнике АК всех сочетаний кода и региона с датой более ранней чем самая ранняя дата в таблице Sales. Если такой даты не будет, то туда съедет категория с более верхней по ходу сортировки группы.
Это я понял. Ваш пример помог мне, еще раз спасибо.

Что касается моего варианта решения, где в результате объединения таблиц или группировки формируется таблица с вложенной таблицей.
При необходимости проведения вычислений с данными (например нужно провести интервальный поиск и вычислить среднее значение, или медиану ) из вложенной таблице возникают описанные выше проблемы: При обработке больших массивов, PQ выжирает оперативную память и компьютер зависает.
Подскажите, есть ли варианты оптимизации "PQ кода" из моего примера, где должен статься примем формирования таблицы с вложенной таблицей в результате объединения.?

Спасибо
 
 
 
#8
24.11.2021 09:58:06
Neostt, а вы по ссылке из сообщения в телеграмме PooHkrd проходили?
Вот конечный код по тому способу:
Скрытый текст

Часть с группировкой можно переписать и с шагами:
Скрытый текст

Или с let, если так привычнее:
Скрытый текст

И делайте внутри группировки все, что Вам хочется:)
 
 
 
#9
24.11.2021 10:12:31
С формированием "группировки" проблем нет.
Описанная мной проблема возникает, с шагом вычислений внутри группировки...
 
 
 
#10
24.11.2021 10:36:52
Дайте пример вычисления, с которым возникает проблема.
 
 
 
Страницы: 1
Наверх