Join with a Null Field

Afternoon Everyone,

Ok so I am having an issue with a join that has a null value in one of the fields.

For my project I am using elements of an accounting string as my join clauses.  My design is to have a table that holds accounting string data WITHOUT transaction dates or amounts.  My other table will have accounting string data WITH transaction dates and amounts.  This is my best effort to create a one to many relationship.

I am working in Tableau 2018.1.  For my example I have taken us all back to junior high biology class so our accounting string is now:

Kingdom, Phylum, Class, Order, Family, Genus, Species, Name, Transaction Amount, Transaction Date

The left table is:

Kingdom, Phylum, Class, Order, Family, Genus, Species, Name

The right table is:

Kingdom, Phylum, Class, Order, Family, Genus, Species, Name, Transaction Amount, Transaction Date

The problem is trying to see Canis: Name, Transaction Amount and Transaction Date.

My mind tell me that the name is null in both tables, therefore the data should show as they are equal but reality tells me different.

I have tried a couple of alternatives as a join calculation such as:

- A CAST function in my SQL statement

- Boolean logic - IIF(ISNULL(NAME),"",0) or IFNULL(NAME,"")

None of these work.

I eventually want to see both cats and dogs in one table.  Pretend one table lists pending credit card transactions and one table are cleared transactions.  This way managers can see what is spent but not reconciled.  Any ideas?

Thanks,

Scott

Michael Hesser