I am trying to populate a sparse column based on values of the same column. I attached an example.
I have a table like the following:
Id, Before, Group
1, None, 1
2, 1, -
3, None, 2
4, 2, -
5, 4, -
6, 3, -
To fill the missing values I intend to lookup the value in Before in the Id column and receive the value of Group. For example in line 2 i have the value 1 in "Before", so it should look in line 1 (as there is "Id" 1) and return 1 for "Group".
I want to receive the following result:
Id, Before, Group
1, None, 1
2, 1, 1,
3, None, 2
4, 2, 1
5, 4, 1
6, 3, 2
I am using LEFT JOIN right now to lookup the missing groups and I understand that this will not work because the column group already exsits. But in fact I do not know how to deal with it. Can anyone help?
Well, I would advise that you step back and rethink what a possible unambiguous link key could be.
It might not be in your current set of data, so it might also be good to check with the data owners what information could be used in addition to what you already have available.
Maybe I am missing something (and for sure I don't understand the business cases behind) but I don't see that what you've described so far results in a stable solution (for example, arrival of line 1 is past departure of line 4, a condition you excluded in a previous post, but you still want these lines connected).
