This is exactly how I am currently hadling it in my script. Only change is instead of TransactionID, I am using RowNo() in my script.
But the key issue I am facing here is, because the NameAddress table has close to 2M records, having 3 copies of it and again concatenating to create a new table is taking quite a lot of time to load data and also more RAM space when users access this using access point.
I posted this here to find out what I am doing is correct or there any other better ways to handle this.
Are the UnderwriterID and Thirdparty different for the ClientID in the two tables? If they are, then I would rename them as something different in one of the two tables and link only on the ClientID.
Eg LOAD UndewriterID AS ClientUnderwriterID, Thirdparty AS ClientThirdparty.... for NameAddressData
If the ClientID can have transactions with many combinations of UnderwriterID and Thirdparty, (ie the key to the transaction table is ClientID, UnderwriterID and Thirdparty), then you need to decide whether you need the UnderwriterID and Thirdparty in the NameAddress table - ie what purpose do they serve?
If you need to link on all three fields (ie the key to the relationship is all three fields, you can allow QV to create a synthetic key, or create your own composite key. Another option is to merge the two tables to create a single fact table. The best option depends on your data model and desgin objectives.
Remember that QV creates an association between two tables by using a field of the same name in the two tables, so you would use:
LOAD NameID as ClientID .... for the NameAddress data
Hope these help
Logic will get you from a to b. Imagination will take you everywhere. - A Einstein