I'm sure this is a common enough issue, but I cannot get my head around how to fix it. In my opinion it is a problem with the way the original data is set up but I don't think that can be helped at this stage.
I have two Tables:
So as you can see, both tables have a UPRN or a USRN column
I need to be able to filter the Enquirys table down using the Ward Name
Each row in EnquirySummary will only have either a UPRN or a USRN, not both. The AddressInfo table holds both.
As it is, Qlikview willl create a synthetic table inbetween.
I tried creating a unique Key of combined UPRN and USRN concatenated but it wont work as obviously the records in EnquirySummary will never have both values in their key.
Not sure, but think that might mess up your counts, if you want to calculate the average #enquiry's per ward (cos wards are now duplicated).
I think I would resolve this during load by replacing all the USRN's in enquirysummary with the corresponding UPRN's during LOAD. That way you only link to UPRN's. If you need to know if it originally was a USRN or UPRN, add a boolean is_USRN.