Hello
I've got two data tables I want to join together in an unusual way.
There are two key fields but the lookup file might only specify one of them. If it does then that lookup value needs to apply to all records with that value.
The example might clarify.
For code A, in the lookup, codes have been defined for groups A2 and A3
For code B, the lookup only specifies it at that level so the lookup value (blue) is applied to all B records
For code C, again the lookup specifies it at code 1 and code 2 level
The D code lookup is only specified at the code 1 level, so the value (black) is applied to all D records.
I was thinking I could read the lookup file and split it depending on whether it has a code 2 value or not. Then do a join at the code1/code2 level and another at the code1 only level. But I don't really know how to script this.
I want the full lookup code1/code2 to override the code1 only lookup.
Hope this makes sense, any help would be appreciated!
Thanks.
