Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have two tables sales and order. Both tables have date columns say sales date and order date . These two tables are joined using key product ID.
I want to have a date field available which would link both dates and filter the tables. For example, if I select Jan from this field, I should be able to compare order and sales amount from January.
What would be the best way to model my table to achieve this? Any help?
You are getting incorrect results because you are using EmpId as the key field. That is not granular enough, the EmpId appears across multiple periods. The correct Key is
[Emp ID] & [Client ID] & [R Period] as Key
That will still give you a synkey for Key and EmpId, which is logically correct, but a bit ugly. I agree with Oleg that a concatenated Fact table is better for this case. The attached example shows both.
-Rob
Thank you for giving the links, I will go through them. Meanwhile, if anyone has already implemented this please share your solution.
The links swuehl provided do provide solutions. The last one provides a working qvw example that matches your case.
-Rob
Hi Rob
Excellent tutorial. I tried to implement the same and am seeing some issues can you please suggest what would be the problem?
The table which says month is not breaking down the values accordingly. Sample application attached
Hi Praveen,
you have two fact tables - Sales and Order, which you linked by Product ID. In reality, these two tables should have more common dimensions. For example, Customer - both Sales and Orders tables should have a customer dimension. Date dimension, that you mentioned, is also common. There might be more, depending on your business scenario - shipping address, delivery method, etc.
The best possible way of working with two Fact tables like these two, is to concatenate the Facts into a Single Fact data model. This method also involves the process of finding and restoring missing associations - those associations that existed when you linked the two tables, and disappeared when you concatenated them.
As a result of concatenating the two Fact tables, the two Date fields naturally converge into a single Date field that can be linked to a single Master Calendar.
I'm describing this process in detail in my new book, QlikView Your Business. Check it out!.
Cheers,
Oleg Troyansky
I think the issue is with how we form the key. In the tutorial, order ID doesn't repeat more than once.
In my example, emp id repeats. I don't have a unique field to identify every row.How do I remodel my fact tables ?
You won't have these issues if you follow my advise and concatenate the two tables.
Oleg,
Please, do tell how it would be possible to recreate the lost associations with the dimensional data once the two fact tables are concatenated. Would we need to reload those dimensions/call them from a resident load and left join? Or, even simply have a linking key to re-associate the lost relations?
Thanks,
Ron
Can you please help with the sample file I provided?