Join with null values

[felipeecst]

Hi everyone

I have the following table:

Benefit	Company	Location	Unit	Track	Level	Value
Transport						1830
Medical						8241
Car Plan					5	26217
Car Plan					6	23222
Car Plan					7	21054
Car Plan					8	18998
Lunch		LocA				25
Lunch		LocB				26
Lunch		LocC				32

Except for Benefit and Value columns, all other columns can assume null values. In this cases, it means that the Benefit has the same Value for all the possible values of that column.

I have another table that has all possible combinations for the columns Company, Location, Unit, Track and Level, but I want to join them considering the null columns, to add the Value column to this other table.

For example, in the first line, it means that the benefit has the same value for all the possible combinations of Company, Location, Unit, Track and Level. In the third line, it means that the benefit has the same value for all the possible combinations of Company, Location, Unit and Track for that specific Level.

Can anyone help me doing that?

Thanks in advance.

[jdunkerley79 · Accepted solution]

A fun challenge :)

Here is my suggestion.

For each row in the table below, create a JoinOn and JoinValue using a formula tool. The JoinOn will be the condition to evaluate each row in the second data set against and the JoinValue will be the equivalent from the table below. Making the assumption that only Level is numeric something like:

Join On

IIF(IsNull([Company]),"","Company|")+
IIF(IsNull([Location]),"","Location|")+
IIF(IsNull([Unit]),"","Unit|")+
IIF(IsNull([Track]),"","Track|")+
IIF(IsNull([Level]),"","Level|")+
'1'

The final 1 copes with the all null cases

Join Value

IIF(IsNull([Company]),"",[Company]+'|')+
IIF(IsNull([Location]),"",[Location]+'|')+
IIF(IsNull([Unit]),"",[Unit]+'|')+
IIF(IsNull([Track]),"",[Track]+'|')+
IIF(IsNull([Level]),"",tostring([Level],0)+'|')
+'1'

Very similar to JoinOn but evaluating the values

Having done that create a unique set of JoinOn values

Use an Append Fields to cartesian join these to the second dataset.

For the second dataset we need to compute the JoinValue for each JoinOn case. THe easiest way is to do a replace formula, something like:

Replace(Replace(Replace(Replace(Replace(
[JoinOn], 'Company', [Company])
        , 'Location', [Location])
        , 'Unit', [Unit])
        , 'Track', [Track])
        , 'Level', ToString([Level]))

Finally join back to original set on both JoinOn and Join Value and we are done

[felipeecst]

That's it! Thank you @jdunkerley79! :)