none
Table.Join/Table.NestedJoin - JoinAlgorithm options. Please doc./explain RRS feed

  • Вопрос

  • Hi

    _________________________________________

    EDIT (follow. Ehren reply) - JoinAlgorithm options:

    Dynamic
    Automatically chooses a join algorithm based on inspecting the initial rows and metadata of both tables.

    PairwiseHash
    Buffers the rows of both the left and right tables until one of the tables is completely buffered, and then performs a LeftHash or RigthHash, depending on which table was buffered completely. This algorithm is recommended only for small tables.

    LeftHash
    Buffers the left rows into a lookup table and streams the right rows. For each right row, the matching left rows are found via the buffered looptup table. This algorithm is recommended when the left table is small and most of the rows from the right table are expected to match a left row.

    RightHash
    Buffers the right rows into a lookup table and streams the left rows. For each left row, the matching right rows are found via the buffered lookup table. This algorithm is recommended when the right table is small and most of the rows from the left table are expected to match a right row.

    LeftIndex
    In batches, uses the keys from the left table to to a predicate-based queries against the right table. This algorithm is recommended when the right table is large, supports folding of Table.SelectRows, and contains few rows which are expected to match a left row.

    RightIndex
    In batches, uses the keys from the right table to do predicate-based queries against the left table. This algorithm is recommended when the left table is large, supports folding of Table.SelectRows, and contains few rows which are expected to match a right row.

    SortMerge
    Performs a streaming merge based on the assumption that both tables are sorted by their join keys. While efficient, it will return incorrect results if the tables aren't sorted as expected

    _________________________________________

    Below code is a simplified version of part of a bigger query for another thread on this forum. At some point need to Join 2 tables of Dates (in this case) and keep the Dates of the 1st table ordered. Ideally don't want to Sort that 1st table after Joining it

    After n experimentations, finally got the expected result with Table.Join option JoinAlgorithm.SortMerge. However I couldn't find any doc./explain. regarding the various JoinAlgorithm options so have no clue at all what the implication(s) is/are of using - in this case - JoinAlgorithm.SortMerge

    Would highly appreciate if someone could put some clarity into this, would probably be beneficial to a number of PQ users. Thanks in advance

    let
        userDates = List.Combine({{#date(2016,1,1)}, {#date(2020,12,31)}}),
        Holidays =
            let
                Source = #table(type table [PH_Date=date], List.Zip({List.Generate(()=> #date(2016,1,1), each _ <= #date(2020,12,31), each Date.AddDays(_, 27))}))
            in
                Table.SelectRows(Source, each ([PH_Date] >= userDates{0}) and ([PH_Date] <= userDates{1})),
        Calendar = #table(type table [C_Date=date], List.Zip({List.Dates(userDates{0}, Duration.Days(userDates{1} - userDates{0})+1, #duration(1,0,0,0))})),
        JoinTables =
            let
                JoinedCalendarWithHolidays = Table.Join(Calendar,"C_Date", Holidays,"PH_Date", JoinKind.LeftOuter, JoinAlgorithm.SortMerge)
            in
                JoinedCalendarWithHolidays
    in
        // Keep first month dates only. Just to check result is as expected
        Table.FirstN(JoinTables, 31)

    Just in case corresponding workbook is avail. here







    • Изменено Lz._ 14 января 2020 г. 18:41
    13 января 2020 г. 14:05

Ответы

  • Hi there. If you enter = JoinAlgorithm.Type into the PQ Editor formula bar, you should see some documentation.

    Ehren

    • Помечено в качестве ответа Lz._ 14 января 2020 г. 18:40
    13 января 2020 г. 23:26
    Владелец
  • I see SortMerge on my machine. Unfortunately there appears to be a bug (which I've now filed) where the documentation doesn't have a scrollbar in the PQ Editor. If you maximize the Editor, hopefully you can see SortMerge as well.

    Ehren

    • Помечено в качестве ответа Lz._ 14 января 2020 г. 18:40
    14 января 2020 г. 17:42
    Владелец

Все ответы

  • Hi there. If you enter = JoinAlgorithm.Type into the PQ Editor formula bar, you should see some documentation.

    Ehren

    • Помечено в качестве ответа Lz._ 14 января 2020 г. 18:40
    13 января 2020 г. 23:26
    Владелец
  • If you enter = JoinAlgorithm.Type into the PQ Editor formula bar, you should see some documentation.

    Very beneficial Ehren, THANKS a lot (initial post updated with documented Algo.). We're "only" missing JoinAlgorithm.SortMerge. Any chance we get something doc. (in a way or another) in the next few days/weeks?

    Thanks much again

    14 января 2020 г. 16:08
  • I see SortMerge on my machine. Unfortunately there appears to be a bug (which I've now filed) where the documentation doesn't have a scrollbar in the PQ Editor. If you maximize the Editor, hopefully you can see SortMerge as well.

    Ehren

    • Помечено в качестве ответа Lz._ 14 января 2020 г. 18:40
    14 января 2020 г. 17:42
    Владелец
  • If it helps, you can also see the descriptions using the following expression:

    = Value.Metadata(JoinAlgorithm.Type)[Documentation.LongDescription

    14 января 2020 г. 18:12
  • Hey Ehren

    PQ was already maxed. on my laptop (max. resolution 1366x768) and you're right no scrollbar. Anyway, with Ben Programmer suggestion, can now read: Performs a streaming merge based on the assumption that both tables are sorted by their join keys. While efficient, it will return incorrect results if the tables aren't sorted as expected

    Will update initial post & close. Thanks much again (to Ben as well -> already Upvoted)

    14 января 2020 г. 18:34