Forum Discussion

robofski's avatar
robofski
Icon for Resolver II rankResolver II
9 years ago
Solved

Exists in 3 tables

Hi community,

 

I have 3 tables, one from AD, one from SCCM and one from another Audit program, all three tables contain Computer Name.

 

I'm trying to determine which computers appear in all 3 tables, which in two and which only in one.

 

There are computers that exisit in only in one of the 3 tables which means I don't really have a good place to start.

 

Anyone have any ideas?

 

Dan

 

Here's an example of the data, each column is a table:

 

ADSCCMTrackIT
Computer1 Computer1
Computer2  
Computer3  
Computer4Computer4Computer4
Computer5 Computer5
  Computer6
Computer7Computer7Computer7
Computer8Computer8Computer8
 Computer9Computer9
  Computer10
Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    Hi robofski,

    You'd better create new table to display the computers displayed in both or three tables. For instance, the first formula shows all the computers appear both AD table and SCCM table. The second formula shows all the computers appear in AD ,SCCM and TrackIT table.

    AD&SCCM = INTERSECT(AD,SCCM)
    AD&SCCM&TrackIT = INTERSECT('AD&SCCM',TrackIT)


    If you determine Computer4 in all there tables. You can create a calculated column using the following formula and get the result shown in screenshot. If there is a yes flag, which prove the Computer appears in all the three tables.



    For another example, if you create a calculate column in SCCM&TrackIT table to determine Computer9 use the similar method.

    SCCM&TrackIT = INTERSECT(TrackIT,SCCM)


    Compare the following first and second screenshot in AD&SCCM&TrackIT and SCCM&TrackIT tables. You will find Computer9 appear in two tables, SCCM and TrackIT tables, doesn't appear in AD table.


    If you have any other issue, please feel free to ask.

    Best Regards,
    Angelia

2 Replies

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi robofski,

    You'd better create new table to display the computers displayed in both or three tables. For instance, the first formula shows all the computers appear both AD table and SCCM table. The second formula shows all the computers appear in AD ,SCCM and TrackIT table.

    AD&SCCM = INTERSECT(AD,SCCM)
    AD&SCCM&TrackIT = INTERSECT('AD&SCCM',TrackIT)


    If you determine Computer4 in all there tables. You can create a calculated column using the following formula and get the result shown in screenshot. If there is a yes flag, which prove the Computer appears in all the three tables.



    For another example, if you create a calculate column in SCCM&TrackIT table to determine Computer9 use the similar method.

    SCCM&TrackIT = INTERSECT(TrackIT,SCCM)


    Compare the following first and second screenshot in AD&SCCM&TrackIT and SCCM&TrackIT tables. You will find Computer9 appear in two tables, SCCM and TrackIT tables, doesn't appear in AD table.


    If you have any other issue, please feel free to ask.

    Best Regards,
    Angelia

  • tringuyenminh92's avatar
    tringuyenminh92
    Icon for Memorable Member rankMemorable Member
    9 years ago
    Hi robofski,

    In modeling tab, new table and Please try with intersect() and values() method. You could firstly use values() to get computer name column and distinct and put it inside intersect() method (twice cause this method receive 2 parameters)