Compare columns in two different tables, provide specific name for not matching records

[Anonymous]

Hi all,

I will simplfy this and cut out all the extra data around it, essentially I have two tables

Name

• id
• name

Which contains everyone. 

and

Region

• id 
• region

Region only contains ids of those with a specificed region. 

What I want to do is compare the two id fields, and where there's no matching id for the 'region' (but still a record in the name table) I want there to be a line added to the Region table with the missing ID and the region set as 'Undefined'.

I have tried with an IF statement, but even though the logic feels like it should be relatively straightforward (If the 'id' fields don't match then 'Undefined' otherwise [region]), I am struggling to get something working. 

It may be that I need to revisit the data export and see if I can include ids with blanks for the undefined regions to allow for an easier matching process (as currently it would require inserting both the missing ID and 'Undefined' columns). 

[goncalogeraldes]

Hello there @Anonymous ! As you have correctly said, go back to the original data export and keep the NULL/BLANKS and then use a calculated column with the following:

New_Region_Column =
IF (
    Name[id] IN DISTINCT ( Region[id] ),
    SELECTEDVALUE(Region[Region]),
    "Undefined"
)

Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍
Thanks!

You can also check out my LinkedIn!

Best regards,
Gonçalo Geraldes

[Anonymous]

@goncalogeraldes Thank you - I will have to review how the data is currently exported and stored, it isn't a case of me deleting blank records, it typically only exports the records with data.

Once I've solved that I can give the calculated column you suggest a try!