Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Объединение (слияние) запросов с фильтром в PQ. Функция Table.NestedJoin
 
#1
23.09.2019 10:47:36
Коллеги, Добрый день!

Форум читал… ответа не нашел...

Продолжаю изучать PQ и в очередной раз напоролся на тупик.

Есть задача, которая во вложении. Суть: имеем 2 таблицы – 1-звонки менеджеров по клиентам и 2- продажи товара по клиентам. И между ними нет прямой связи. Нужно определить, после звонка какого сотрудника была продажа и присвоить ее этому звонку.

Проблема заключается в том что я не могу найти способ как в процесс объединить запрос (слияние) в Table.NestedJoin добавить сопоставление не только по 100% совпадению колонок, а и по фильтру > , <, =…

Я нашел костыльный вариант как обойти это через буферную таблицу с кучей расчетов (пример во вложении) … Но когда положил это на реальную базу, скрестить 2 таблицы за год где в каждой в месяц по 1+млн строк не получается…

Помогите советом, наверное, можно объединить эти таблицы более простым и менее ресурсоемким способом. Я уже приуныл ковырять интернет и скудный мануал от майкрософта….

Заранее спасибо за помощь!
 
 
 
#2
23.09.2019 10:58:15
Цитата
dknox написал:
Нужно определить, после звонка какого сотрудника была продажа и присвоить ее этому звонку
а хоть какие-то более конкретные критерии есть? Только дата звонка и дата продажи? Даже если смотреть на таблицы совсем нет уверенности, что результатом звонка от 1 января является продажа от 2 января, а не 3-го или даже 5-го. Так же неясно, что делать с двойными продажами: те же 2 и 3 января. Это все должно быть результатом именно звонка от 1 января или только одна продажа? Судя по приведенному результату - обе. Но. Это же лишь примерные таблицы, в реале данных больше. Так вот представим: был звонок 1-го, продаж не было. был звонок 2-го и одна продажа 2-го. 3-го было звонок и две продажи. как распределить, если в реальности продажа от 2-го была результатом звонка 2-го, а 3-го - от 1-го и 3-го? Или это неважно, главное хоть как-то раскидать?
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
#3
23.09.2019 11:14:35
Сделал как понял. Результат не совсем такой как в примере, но логика объединения таблиц думаю понятна. Для себя уже напильником доработаете, ну или спросите как доработать.
Код
let
    Sales = Table.RenameColumns( Excel.CurrentWorkbook(){[Name="sale"]}[Content], {{"Дата продажи", "Дата"}} ),
    Calls = Table.RenameColumns( Excel.CurrentWorkbook(){[Name="call"]}[Content],{{"Дата звонка", "Дата"}} ),
    Union = Table.AddColumn(Calls, "Операция", each "Звонок") & Table.AddColumn(Sales, "Операция", each "Продажа"),
    ChangedType = Table.TransformColumnTypes(Union,{{"Дата", type date}, {"Сотрудник", type text}, {"Контрагент", type text}, {"Операция", type text}, {"Продукт", type text}}),
    GroupedRows = Table.Group(ChangedType, {"Контрагент"}, {{"Таб", each Table.FillDown( Table.Sort( _, {{"Дата", Order.Ascending}} ), {"Сотрудник"} ), type table}}),
    Combine = Table.Combine( GroupedRows[Таб] )
in
    Combine

Работать по идее должно быстро и на 1кк+ строк.
Вот горшок пустой, он предмет простой...
 
 
 
#4
23.09.2019 11:23:48
Доброе время суток
Цитата
PooHkrd написал:
Или это неважно, главное хоть как-то раскидать?
Раньше это называлось подтасовать  :D
Цитата
PooHkrd написал:
Сделал как понял.
Ещё одно - как понял
Код
let
    call = Excel.CurrentWorkbook(){[Name="call"]}[Content],
    addCallId = Table.AddIndexColumn(call, "id"),
    addCallPriority = Table.AddColumn(addCallId, "priority", each 0),
    sales = Excel.CurrentWorkbook(){[Name="sale"]}[Content],
    addSalePriority = Table.AddColumn(sales, "priority", each 1),
    addSaleCall = Table.AddColumn(addSalePriority, "Дата звонка", each [Дата продажи]),
    merge = addCallPriority & addSaleCall,
    #"Sorted Rows" = Table.Sort(merge,{{"Дата звонка", Order.Ascending}, {"priority", Order.Ascending}}),
    #"Filled Down" = Table.FillDown(#"Sorted Rows",{"id"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Продукт] <> null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Дата звонка", "Сотрудник", "priority"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"id", "ids"}, {"Контрагент", "Контрагент-s"}}),
    result = Table.Join(addCallId, {"id"}, #"Renamed Columns", {"ids"}, JoinKind.LeftOuter)
in
    result
 
 
 
#5
23.09.2019 11:46:40
Цитата
Дмитрий(The_Prist) Щербаков написал:
Так вот представим: был звонок 1-го, продаж не было. был звонок 2-го и одна продажа 2-го. 3-го было звонок и две продажи. как распределить, если в реальности продажа от 2-го была результатом звонка 2-го, а 3-го - от 1-го и 3-го?

Цитата
Андрей VG написал:
Раньше это называлось подтасовать   Цитата

В этом примере да, про подтасовать согласен. Но в жизни логика "кто последний тот и папа продал" работает в 99% случаев.

А вообще вопрос из-за криворукости разработчиков системы... Приходится выкручиваться.
 
 
 
#6
23.09.2019 11:47:21
PooHkrd, Андрей VG,

Спасибо за пример!, сейчас буду смотреть
 
 
 
#7
23.09.2019 11:52:15
Цитата
dknox написал:
А вообще вопрос из-за криворукости разработчиков системы...
А в чем криворукость если не секрет? Чтобы связать в системе звонки с продажами, по идее нужно чтобы при продаже клиент заявлял вам что именно общение с господином Ивановым сподвигло его закупить у вас персики, а в системе можно было бы зафиксировать данный факт. Вы так это видите?
Вот горшок пустой, он предмет простой...
 
 
 
#8
23.09.2019 12:04:10
Цитата
PooHkrd написал:
А в чем криворукость если не секрет?

Нужно иметь сущность предварительного заказа, в которой будут 2 роли для звонящих - инициатор продажи и консультант и будет зафиксирован факт интереса клиента. И на основе этих ролей распределять продажи и плюшки за помощь. А сейчас по сути в системе нет ничего про связки продаж и живет правило что каждый продажник волк и все продажи его.
 
 
 
Страницы: 1
Наверх