Forum Discussion
How do I dynamically merge two columns that contain both matching and unique values?
I have the following table that is the result of doing a full outer join of two tables (UNAIDEDAWARENESS and FAMILIARITY), matching uuids and matching Service:
They both have most services in common ('Acontra+', 'Acorn TV', etc...), but they also have a few extra services not in common ('ViX' is only in the first table, and 'Other services' is only in the second table, for example). Where they are not in commong, it will have the service in one column, and 'null' in the other.
I want to merge the two 'Service' columns into one single column, keeping in the rows containing the extra services that are not in common.
If I simply click 'merge columns', the issue is that it doubles up the names for services that are in commong (e.g. "Acontra+Acontra+", or whatever delimiter you choose), and for the services not in common, it produces the correct result (e.g. just "ViX").
So you'd think that it's just a matter of replacing all values in common in one of the columns with 'null'. However, I don't want to do this by hand, in case the data changes in the future. I want it to be calculated dynamically, determining which values are in common between the two columns and deleting those values from one column, and then merging them.
Is this the best way to go?
If so, how would I achieve this?
Figured it out. I added a new custom column and entered the following code:
= each if [UNAIDEDAWARENESS.Service] = null then [FAMILIARITY.Service] else if [FAMILIARITY.Service] = null then [UNAIDEDAWARENESS.Service] else [UNAIDEDAWARENESS.Service])So basically where either column is null, do the value in the other column, and where they match (which is all other cases), just pick the first one (arbitrary).
Then did the same for uuids.
1 Reply
Figured it out. I added a new custom column and entered the following code:
= each if [UNAIDEDAWARENESS.Service] = null then [FAMILIARITY.Service] else if [FAMILIARITY.Service] = null then [UNAIDEDAWARENESS.Service] else [UNAIDEDAWARENESS.Service])So basically where either column is null, do the value in the other column, and where they match (which is all other cases), just pick the first one (arbitrary).
Then did the same for uuids.