Страницы: 1
RSS
PQ. Полное внешнее соединение по группам
 
Добрый день, Друзья.

Часто сталкиваюсь с кейсом, изложенным ниже, решать его приходится, как по мне не совсем корректно.
Есть таблица, условно с планами и справочник товаров по группам. Бывают ситуации, когда какие-то позиции в планах отсутствуют, но в Справочнике товаров в какой-либо группе они есть. Эти товары (которых нет в планах) нужно добавить в основную таблицу по сцепке дата - сценарий - группа. (Во вложении пример). Нужно что-то вроде внешнего соединения, мне кажется. Но пока вопрос приходится решать тем, что в группу добавлять все товары, а уже после подтягивать из первой таблицы данные..
Может кто-нибудь помочь с вопросом?  
Изменено: OblivionR - 30.03.2020 10:52:45
 
Простите, файл примера, не том прикрепил, сейчас изменю...(
(Поправил)
Изменено: OblivionR - 30.03.2020 10:33:27
 
на базе имеющихся запросов:
Код
let
    lst = List.Buffer(Источник[Артикул]),
    dob = Table.SelectRows(Справочник, each not List.ContainsAny({[Артикул]},lst)),
    group = Table.Group(Источник,{"Дата","Сценарий"},{"tmp", each _}),
    comb = Table.TransformColumns(group,{"tmp", each Table.Combine({_,dob})}),
    comb2 = Table.Combine(comb[tmp]),
    to = Table.FillDown(comb2,{"Дата", "Сценарий", "Номер"})
in
    to
Соблюдение правил форума не освобождает от модераторского произвола
 
.
Изменено: Максим Зеленский - 30.03.2020 11:22:19 (замена исходного файла)
F1 творит чудеса
 
Мой код накликан на предыдущей версии файла, но поясните все же, что у вас внутри таблицы происходит?
Столбец Да/Нет как должен заполняться для добавляемых строк - потому что сценарий, или потому что дата, или почему в одном случае 0, в другом стоит 1?
В столбце план должен стоять 0?
F1 творит чудеса
 
Максим Зеленский, добавленные позиции, наследуют значения, если можно так сказать, верхних строк, кроме Клиента и Плана, по плану будет стоять 0,
Цитата
Максим Зеленский написал:
почему в одном случае 0, в другом стоит 1?
такова исходная таблица, у какого-то номера 0, у какого-то 1
, buchlotnik сделал прямо по ТЗ 1-ого примера, осталось только заполнить значениями сверху.

Постараюсь объяснить зачем мне это, в BI есть 2 таблицы, артикулы выступаю в качестве каркаса, если в одной таблицы нет позиций из другой, то общая сумма подтягивается некорректная (эти позиции выпадают), вот я решил таким способом устранить проблему,
Спасибо
 
Цитата
OblivionR написал:
вот я решил таким способом устранить проблему,
это очень плохая идея
Вот у вас есть такая модель:
Данные1 <- Справочник -> Данные2
В справочнике все артикулы, которые есть в Данные1 и Данные2, или даже больше.
Если вы берете для аналитики столбец артикула из справочника, у вас ничего ниоткуда не выпадет. А если будете брать артикулы из одной из таблиц Данные, то конечно это приводит к выпаданию непересекающихся артикулов, но кратно увеличивать количество строк в таблицах Данных для того, чтобы там были все артикулы - это ОЧЕНЬ плохая идея.
F1 творит чудеса
 
Цитата
OblivionR написал:
файл примера, не том прикрепил
тогда так
Код
let
    from = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    group = Table.Group(from, {"Дата", "Сценарий"}, {{"tmp", each _}}),
    add = (tb1,tb2) =>
            [
                a = tb1[Артикул],
                b = Table.SelectRows(tb2, each not List.ContainsAny({[Артикул]},a)),
                c = Table.Combine({tb1,b})
            ][c],
    trf = Table.TransformColumns(group, {"tmp", each add(_,Справочник)}),
    comb = Table.Combine(trf[tmp]),
    to = Table.FillDown(comb,{"Дата", "Сценарий", "Номер","Да/нет"})
in
    to
Соблюдение правил форума не освобождает от модераторского произвола
 
Максим Зеленский, да я понимаю, что это не очень идея, но другого варианта в своей ситуации найти не могу(
buchlotnik, все работает, только одно но, если в справочнике несколько групп, то он их добавляем в каждую сцепку, посмотрите?
 
OblivionR, так по первому или по второму примеру нужно решение? Это же разные вещи
Соблюдение правил форума не освобождает от модераторского произвола
 
buchlotnik, по второму, просто я посмотрел, что и в перовом, что и во втором, добавляет все группы
Изменено: OblivionR - 30.03.2020 11:56:49
 
Скрытый текст
Соблюдение правил форума не освобождает от модераторского произвола
 
buchlotnik, спасибо, то что надо! Осталось разобраться в коде)
 
Цитата
OblivionR написал:
но другого варианта в своей ситуации найти не могу
А может все таки покажете что вам нужно получить именно в итоговом отчете в сводной при таких-то исходных данных. Вдруг кто-то кроме вас другой вариант найти сможет?  ;) То что вы придумали это чудовищный костыль, который не позволит легко и непринуждённо строить отчеты на таком сете данных.
Вот горшок пустой, он предмет простой...
 
PooHkrd, показать не могу, рабочий файл, весом под гиг, с конфиденциальной инфой, и кучей таблиц...это анриал(
 
А вот что-то соорудил на основе List.Accumulate

Код
let
    Источник = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    #"Измененный тип" = Table.TransformColumnTypes(Источник,{{"Дата", type date}, {"Сценарий", type text}, {"Номер", Int64.Type}, 
   {"Группа", type text}, {"Клиент", type text}, {"Артикул", type text}, {"План", Int64.Type}, {"Да/нет", Int64.Type}}),
    #"Сгруппированные строки" = Table.Group(#"Измененный тип", {"Дата", "Сценарий", "Группа"}, 
        {{"tmp", each _, type table [Дата=date, Сценарий=text, Номер=number, Группа=text, Клиент=text, Артикул=text, План=number, #"Да/нет"=number]}})[tmp], 
    a = List.Accumulate(#"Сгруппированные строки", Table.FirstN(#"Сгруппированные строки"{0}, 0), (state, current) => 
              Table.Combine({state, Table.Distinct(Table.Combine({current, Table.SelectRows(Справочник, each [Группа]=current[Группа]{0})}), {"Артикул"})})),
    b = Table.FillDown(a,{"Дата", "Сценарий", "Номер", "Артикул", "Да/нет"})
in
    b

 
quasarrr, тоже здорово))
 
Цитата
OblivionR написал:
quasarrr , тоже здорово))
Да вот сейчас увидел один скользкий момент...

Фрагмент формулы из List.Accumulate:  
Цитата
Table.Distinct(Table.Combine({current, ...
Сработает корректно или нет в зависимости от того, как функция Table.Distinct реализована. У функции есть выбор, какую повторяющуюся строчку из таблицы убрать. Не очевидно, что выберет ту, которая пришла из справочника. Если всегда повторы сверху вниз рассматривает, то всё хорошо. Ну а если возможны варианты, то может работать нестабильно. На примерах, которые тут были отрабатывает правильно.

Но возможно необходимо принять дополнительные меры, чтобы отсеивала именно пустые повторяющиеся строки из справочника.
 
Например, так
Код
let
    Источник = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    #"Измененный тип" = Table.TransformColumnTypes(Источник,{{"Дата", type date}, {"Сценарий", type text}, {"Номер", Int64.Type}, {"Группа", type text}, {"Клиент", type text}, {"Артикул", type text}, {"План", Int64.Type}, {"Да/нет", Int64.Type}}),
    #"Сгруппированные строки" = Table.Group(#"Измененный тип", {"Дата", "Сценарий", "Группа"}, 
        {{"tmp", each _, type table [Дата=date, Сценарий=text, Номер=number, Группа=text, Клиент=text, Артикул=text, План=number, #"Да/нет"=number]}})[tmp], 
    a = List.Accumulate(#"Сгруппированные строки", Table.FirstN(#"Сгруппированные строки"{0}, 0), (state, current) => 
              Table.Combine({state, Table.Distinct(Table.Combine({current, Table.SelectRows(Справочник, each not List.ContainsAny({[Артикул]},current[Артикул]) and [Группа]=current[Группа]{0})}), {"Артикул"})})),
    b = Table.FillDown(a,{"Дата", "Сценарий", "Номер", "Артикул", "Да/нет"})
in
    b

Подглядел у buchlotnik,  ;)  
 
Сорри, по идее теперь Table.Distinct  лишний
Код
let
    Источник = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    #"Измененный тип" = Table.TransformColumnTypes(Источник,{{"Дата", type date}, {"Сценарий", type text}, {"Номер", Int64.Type}, {"Группа", type text}, {"Клиент", type text}, {"Артикул", type text}, {"План", Int64.Type}, {"Да/нет", Int64.Type}}),
    #"Сгруппированные строки" = Table.Group(#"Измененный тип", {"Дата", "Сценарий", "Группа"}, 
        {{"tmp", each _, type table [Дата=date, Сценарий=text, Номер=number, Группа=text, Клиент=text, Артикул=text, План=number, #"Да/нет"=number]}})[tmp], 
    a = List.Accumulate(#"Сгруппированные строки", Table.FirstN(#"Сгруппированные строки"{0}, 0), (state, current) => 
              Table.Combine({state, Table.Combine({current, Table.SelectRows(Справочник, each not List.ContainsAny({[Артикул]},current[Артикул]) and [Группа]=current[Группа]{0})})})),
    b = Table.FillDown(a,{"Дата", "Сценарий", "Номер", "Артикул", "Да/нет"})
in
    b
Страницы: 1
Наверх