Question:I am trying to combine two tables using left join for Reconciliation and there are two variables which I want to use for match condition. However, there is an additional requirement for one of the matched variable. If there is a match for second variable then pull that row from second table, if not then put the corresponding second value as missing and still extract other variables from the second table. But I am getting missing values if no match exist for second var. I know this is natural sql behavior. But I just want to try if anything can be done.
Here is a sample code:
Output I want
Appreciate your help!
Answer:One thing you might consider is just INTERLEAVING the observations by ID and DATE and just using the latest value of OTHER_VAR on the dates from A.
So if you have this input data:
If you have better answer, please add a comment about this, thank you!