As far as I can see, one table contains sales facts, and the other contains dimensional information around sales. I would try to see if they can be merged into a single fact table (within a star schema). This may require some analysis of the data (possibly beyond the scope of this forum) and some transformation of the data in one or both of the tables.
Linking in the way you seem to be thinking would complicate the expressions in your model and quite probably impair performance. It can also affect accuracy due to problems like double counting errors.
Hi Jonathan, ok how could I perform a merge on these tables, a common key of some kind would need to be created to merge them on. How can that be done in Qlikview scripting?
I don't know how, also because of the star scheme. Some fields are in the linked tables that would need to be concatenated into a key field?
Thanks for any help on the scripting.
It really is hard to say without a proper analysis of the data. Possibly some combination of ProductGroup, Brand, Type etc relating to productid or unique_name. Or perhaps a mapping table (manual or from your sales/ERP system). Anyway, you need some data that associates the sales facts in dataset1 with the dimensions from dataset2.
These are some thoughts on your problem (while I am waiting for a long reload to complete...):
- What is the unique identifier in dataset1 (ie some combination of fields that uniquely identify records in dataset1)?
- What is the unique identifier in dataset2? Is the timestamp a proper date?
- Looking at the data manually, can you see which shopid (and/or unique_name or productid) corresponds to a sales fact? If you can't figure this out manually, then it will not be possible to automate it.
- You describe dataset 2 as a star schema, but I assume it is a single table. You could break that into separate dimension tables, by eg:
LOAD Distinct unique_name,
...and similar for the other dimensions.
If you really dont know how to go about this, you might need to engage a business analyst or a qlikview consultant to assist you.
Table 1 is unique by combining Date, Brand and Type. Same goes for table 2.
Table 2 is one central fact file with keys for the dimensions. Those dimensions are loaded as 3 separate tables for product, productgroup and shops. So wouldn't need to break them out.
The steps to take for a merge would be:
1. create a key for table one by concatenating date, brand and type
2. create a key for table two by concatenating date, brand and type (here, date is in the central fact file and brand and type in the dimensional table)
3. perform a join by adding table 1 to table 2 on the new key field.
I think this would work, just can't get the scripting together.