Fact table data different (granularity?) and joining

[DennisNorton]

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,

[amonjaras · Accepted solution]

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!

[hector_munoz]

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

[marcus_sommer]

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