Improve outer merge output data sets

Found myself to be facing the same issue! I wonder is there an efficient workaround or a solution yet? I can only tell my own case, but the principle seems exactly the same.
I have two datasets, both of them contain the date (month); one contains revenue generated by a client that month, while the second one contains the time spent on the client that month.

• Some months could have both records.
• Some months could have revenue but no records for any time spent.
• Some months could have time spent but no records of revenue.

It leads to this same issue during the merge for me

 

I’m considering a workaround that uses a third dummy dataset that contains every possible Account ID and every possible Month for each account. Starting with this dummy dataset, then merging these two datasets one by one on top of that while keeping the original Account ID and Date as identifiers.

But I’m not sure if there’s any better solution?

Thanks for posting this - This is an interesting problem. 

The way joins work is pretty standard across industry and tools (https://www.w3schools.com/sql/sql_join_full.asp). Keeping Gainsight tools behavior consistent with industry standard means customers can leverage their cross tool learnings. 

That being said, a potential solution that people use is IFNULL and CONCAT. We can replicate that by filtering null values and concatenating - while acknowledging that its more steps than ideal 

@Rakesh

Can you clarify what do you mean by concatenating if the leftover fields in the merge are not necessarily String? It can be Date or Currency or anything else.

I can imagine a potential solution when working with the Rules Engine (IF time spent = null, write Date1 to Date. IF Revenue = null, write Date2 to Date). Basically like you say leveraging filtering null values.

But since this is about Data Designer, I don’t fully understand how to solve that and what role concat plays in your explanation.

@rakesh bumping this up