Overlay Data

[ejogion]

Hi All,

First time using the forum. Its been great so far but I think I finally found a problem no one else has asked yet.

SO, here is the problem.

I have two datasets. The first dataset has null values everywhere except for certain cells where a value was updated per the result of a batch macro dynamic replace process. I want to fill in the null values with dataset values from the second dataset (the old data, without the updated cells) to give myself a new dataset. 

In my words, I want to overlay the second dataset on top of the first and have it fill in the null spaces in the dataset while leaving the cells that already have values full. Thus giving me an updated dataset.

Any thoughts?

[Inactive User · Accepted solution]

Yep this is no problem -  the dataset with nulls, filter out the null records and union the non-nulls with the other data. You will have an issue though as the non-null values most likely have their existing values in the original dataset you just unioned together. To get rid of the originals you will add a source field prior to unioning the data together. The non-null value table will be source 1, and the other table source 2. Once you have your union dataset, sort by the appropriate ID(s) ascending and add the source ascending. Then put a unique tool on top of that and remove records based on the key, excluding source. This will remove the second instance of each duplicated key, which is the existing record from table b/2.

Unique always keeps the first record and removes the 2+.

[ejogion]

This does not work as there is data from the records I would be eliminating that still needs to be put into the new records I have. If the whole record and all its fields were updated then this would work but I need to fill in the null spaces still. 

[Inactive User]

Easy fix for that is to transpose the columns into a single column name/value scenario then apply that logic I specified. Once done, cross tab it back into column headings.

[ejogion]

Attempted it but the problem is that there are about 42 fields I am transposing so each would need its own ID to apply the unique. 

[ejogion]

Did some further research. Would coalesce work? Is there a way I can do that in Alteryx?

[Inactive User · Accepted solution]

They don't each need their own unique ID, just the record does. You can put a record ID tool on before the transpose and group by it to create your record key. When you cross tab back, you group by that same record ID field.

[ejogion]

I think that got it. Thank you so much!