locked
How to build "Greater/less or Equal" relationships between two tables? RRS feed

  • Question

  • Hi,

    Is there any straightforward approachs to realize the following kind of relationships between two tables?

    Table1.process_end_date >= Table2.work_start_date and

    Table1.process_end_date <= Table2.work_end_date

    BTW, there's no common columns for these two tables to do simple joins (inner, outter...).

    Thanks.

    Regards,

    Qilong 

    Monday, January 26, 2015 2:07 AM

Answers

  • The perf won't be great on this if the tables are large, but you could try adding a custom column to Table1 with the following expression:

    = Table.SelectRows(Table2, (table2Row) => [process_end_date] >= table2Row[work_start_date] and [process_end_date] <= table2Row[work_end_date])

    Ehren

    • Marked as answer by Qilong L Tuesday, January 27, 2015 1:10 AM
    Monday, January 26, 2015 7:23 PM

All replies

  • The perf won't be great on this if the tables are large, but you could try adding a custom column to Table1 with the following expression:

    = Table.SelectRows(Table2, (table2Row) => [process_end_date] >= table2Row[work_start_date] and [process_end_date] <= table2Row[work_end_date])

    Ehren

    • Marked as answer by Qilong L Tuesday, January 27, 2015 1:10 AM
    Monday, January 26, 2015 7:23 PM
  • Hi Ehren,

    Thanks so much for your help.

    It works. But I don't quite clear how it works.  Could you explain a bit about what the function name connected with the column name (table2Row[work_start_date]) used for?

    Thanks again.

    Regards,


    • Edited by Qilong L Tuesday, January 27, 2015 2:27 AM
    Tuesday, January 27, 2015 2:11 AM
  • Sure.

    Table.SelectRows filters a given table (in this case Table2) based on a function provided as the second argument.

    (table2Row) => is the start of our filter function. It defines a function that takes one argument, called table2Row. Each row of Table2 will be passed to this function. If the function returns true, the row will be kept. If the function returns false, the row will be filtered out.

    The right hand side of the => is the filter expression. Because we're adding the custom column to Table1, we can reference a field in the current row of Table1 using square brackets (e.g. [process_end_date]). To reference the fields in the current row of Table2, we have to index into the table2Row variable passed to our function (e.g. table2Row[work_start_date]).

    Hope that helps.

    Ehren

    Tuesday, January 27, 2015 6:49 PM
  • Hi Ehren,

    Thanks again for your kind help. Now I'm perfectly clear about that.

    Best,

    Qilong

    Wednesday, January 28, 2015 12:26 AM