Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
Attempting to solidify my understanding of star schemas, and the resources for my particular questions seem limited or touch on very simplistic examples.
1.) I have been struggling to find the correct way to join fact tables together. I have one fact table that is at a transaction level. I want to incorporate sales goals and have what I would consider one level up from the transactions, which is Salesperson. But I also want to view these rolled up to the Manager, and BusinessType levels.
TRANSFACT GOALSFACTS
TransNum SalespersonID
TransDate Month-Year
SalespersonID GoalAmount
CustomerID
BusinessTypeID
ManagerID
TransAmount
I can't just simply join GOALSFACTS to the TRANSFACT table can I? I have many repeated salespersons on the TRANSFACT table and so my goal amount would be repeated too, no?
2.) In this scenario, if I want to look at one of my ALTFACT table amounts by customer, businesstype, or manager (all keys not present in ALTFACT), is the best method to applymap 3x against the TRANSFACT table for all three dimension keys, and then concat both tables into one?
TRANSFACT ALTFACT
TransNum TransNum
TransDate TransDate
SalespersonID SalespersonID
CustomerID FedAmount
BusinessTypeID AltAmount
ManagerID ShipAmount
TransAmount
Regards,
Hello Dennis.
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:
SalespersonID&'-'&Month-Year
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:
JOIN (TRANSFACT)
LOAD
TransNum,
FedAmount,
AltAmount,
ShipAmount
FROM ALTFACT;
Hope this helps!
Hello Dennis.
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:
SalespersonID&'-'&Month-Year
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:
JOIN (TRANSFACT)
LOAD
TransNum,
FedAmount,
AltAmount,
ShipAmount
FROM ALTFACT;
Hope this helps!
Hi Dennis,
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:
MONTHLYFACT:
LOAD *
RESDENT GOALSFACT;
DROP TABLE GOALSFACT;
CONCATENATE (MONTHLYFACT)
LOAD SalespersonID,
Month(TransDate) & '-' & Year(TransDate) AS Month-Year,
Sum(TransAmount) AS TransAmount
RESIDENT TRANSFACT
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.
Regards,
H
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.
- Marcus