Replacing Null Values in Dynamic Column Using Dynamic Reference Information

[Jules22]

I am working with two datasets:

1. Productivity Data: This dataset contains productivity scores for users across various dates, but some entries are null.
2. Leave Data: This dataset records leave information (e.g., "PL" for Paid Leave, "SL" for Sick Leave) for the same users and dates.

Goal: I want to replace the null values in the productivity dataset with the corresponding leave status from the leave dataset.

Could anyone advise on the best way to achieve this? Any suggestions would be greatly appreciated.

Thank you for your help!

[OTrieger]

@Jules22 

I would do it with Find and Replace, only ensuring first that all the fields that need to be replace are Sting type and not numeric type, as Find and Replace working only with string data

[DavidSkaife · Accepted solution]

Hi @Jules22 

Interesting challenge! Here's my take on it - have annotated the tools so you know what its doing:

Key points to note

As it concatenates the data if a value is in both then it will join them, in the screenshot above i've highlighted an example

Fields must be called the same for the Union to work automatically

Any user on the leave data but not on the productivity data will be included in the output

[Jules22]

@DavidSkaife thank u:)