Exists in 3 tables

[robofski]

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:

AD	SCCM	TrackIT
Computer1		Computer1
Computer2
Computer3
Computer4	Computer4	Computer4
Computer5		Computer5
		Computer6
Computer7	Computer7	Computer7
Computer8	Computer8	Computer8
	Computer9	Computer9
		Computer10

[v-huizhn-msft · Accepted solution]

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]

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)