Forum Discussion

__zhe's avatar
__zhe
Frequent Visitor
7 years ago
Solved

Mark Differences Between 2 Table

Hi There,

I have 2 tables, Table 1 and Table 2 and i am trying to mark items that unique/not in each other table

 

 

 

We can find and list with EXCEPT but how I want to just to mark it down ?

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    7 years ago

    __zhe

     

    In that case, you can select the relevant columns first from both tables using "SELECTCOLUMNS" function

    Then apply the above procedure

     

    i.e.

     

    Calculated Table =
VAR firstTable =
    SELECTCOLUMNS ( Table1, "ID", [ID], "Serial", [Serial], "Name", [Name] )
VAR SecondTable =
    SELECTCOLUMNS ( Table2, "ID", [ID], "Serial", [Serial], "Name", [Name] )
VAR Common =
    INTERSECT ( firstTable, SecondTable )
VAR NotCommon =
    EXCEPT ( DISTINCT ( UNION ( firstTable, SecondTable ) ), Common )
RETURN
    UNION (
        ADDCOLUMNS ( Common, "Unique", "No" ),
        ADDCOLUMNS ( NotCommon, "Unique", "Yes" )
    )

12 Replies

    • __zhe's avatar
      __zhe
      Frequent Visitor
      7 years ago

      Zubair_Muhammad , السلام عليكم

      Yes you're right, John should be unique and marked as "Yes" too 

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Community Champion
        7 years ago

        __zhe

         

        Wa alaikumus salam

         

        You can use this calculated table
        From the Modelling Tab>>New Table

         

        Calculated Table =
VAR Common =
    INTERSECT ( Table1, Table2 )
VAR NotCommon =
    EXCEPT ( DISTINCT ( UNION ( Table1, Table2 ) ), Common )
RETURN
    UNION (
        ADDCOLUMNS ( Common, "Unique", "No" ),
        ADDCOLUMNS ( NotCommon, "Unique", "Yes" )
    )