In scenario 1, a JOIN would repeat the goal amount as you point out.
You should just let Qlik associate the tables naturally, that way you will have no repetitions.
Now, since your goal is stated per Salesperson / Month-Year, a simple association by SalespersonID will not do. Instead, you need to calculate Month-Year on the TRANSFACT table from TransDate, so you can build a combined key in both tables:
Then you can omit the SalesPersonID and Month-Year fields in GOALFACTS.
This way, the GoalAmount and TransAmount will compare nicely, and you will also get the rollups you want.
In scenario 2, assuming TransNum is a unique key for both tables, and both TransDate and SalespersonID are the same in both tables for any given value of TransNum, I would Load TRANSFACT first with all its fields, and then simply:
Hope this helps!
I would do the following:
1) I would create a MONTHLYFACT table with the union of all GOALSFACT records as they come from their source and TRANSFACT grouped as follows:
DROP TABLE GOALSFACT;
Month(TransDate) & '-' & Year(TransDate) AS Month-Year,
Sum(TransAmount) AS TransAmount
GROUP BY SalespersonID,
Month(TransDate) & '-' & Year(TransDate)
The final result would be MONTHLYFACT table (monthly records) and TRANSFACT table (detail of transactions) associated by SalespersonID field.
2) This scenario is similar to #1; I would join both tables in 1 with the structure of ALTFACT and the records coming from TRANSFACT grouped by common dimensions with ALTFACT. Later would drop ALTFACT table and union table created and TRANSFACT table would be associated by a key formed by TransNum, TransDate and SalespersonID fields.
I hope it serves to you.
I would in case one convert the year-month from the goals-table into a date per makedate(year, month, 1) as TransDate (or maybe just Date and also duplicating this field within the trans-table and connecting this field with a master-calendar) and then just concatenate this table to the trans-table - I use this approach since years and it worked very well.
In case two you might be able to join the alt-table to the trans-table depending on the key-matching but a mapping would be less risky to remove/duplicate any records whereby you could use multiple mappings or also a single-mapping with concatenated amount-fields like FedAmount & '|' & AltAmount & '|' & ShipAmount and which are splitted again with a subfield(applymap(), '|', n) function.