Count matching records that don't exist in 2nd table

[Anonymous]

I have two tables with one field that matches in each table.  I'd like to figure out how many of the records in Table A do not exist in Table B.  I'm tracking survey results and want to know how many of my locations have not submitted a response to the survey.  Table A contains a list of our facilities, and Table B is a list of survey responses.  Each table contains the same 'facility name' to match, but this will help us follow up with each facility that hasn't submitted yet.

I'd like to create a gauge that counts the total missing distinct facilities, and a list of the facility names that are missing from Table B.

Thank you for the help,

Phil

[MK_QSL]

Can you provide sample (say 10 lines) for both tables to work?

[HirisH_V7 · Accepted solution]

Hi,

May be like this,

For an example tables like this,

Table1:
LOAD * ,
'Table1' As Flag,
RecNo ()as Table1


INLINE [
    Facilty Code, WorkHours
    HZ, 2
    NZ, 3
    BZ, 21
    BG, 1
    BY, 1
    BH, 5
    BK, 6
];


Table2:
LOAD * ,
'Table2' As Flag,
RecNo() as Table2
INLINE [
    Facilty Code, WorkHours
    HZ, 2
    NZ, 3
    BZ, 21
    BG, 1
    BY, 1
];

To know the table count with respect to each other, Here in above tables Flag is created with respect to each table name .

Such that by using this at front end , You can create a table like this ,

Expression for table 1:   Count({<Flag={'Table1'}>}[Facilty Code])

Expression For Table 2:  Count({<Flag={'Table2'}>}[Facilty Code])

Count(Table1)-Count(Table2) in TextBox

Hope this helps ,

PFA,

Hirish