Discussion Board for collaboration on QlikView Scripting.
For a new report that will compare data in one market vs another, I need to link two sets of data. I have one excel file with facts and dimensions in it, the date level is month. The other data set is a fact table with date (day level), country and ID fields that link to files with shop, product and productgroup data. So this a small star schema that needs to link to an excel file. First thing I did was to create a date field on day level for the excel file data in the script, so the dates are on the same granular level. Then a link between the files has to be created. The common level on which both files are unique is for date and brand_type. From these two variables a unique key could be generated. This is as far as I have been able to solve this.
After reading all posts on linking tables, making joins, applymap variable, and the generic keys PDF file on this site, I don't get any closer to a solution. Can someone explain how to do this step by step, as I don't understand the process in Qlikview, and how to use applymap or do the data modeling for this. Thanks!
If excel file data is in date as month level then make Timestamp also as month level..
means if date is 03-2012 then date(Timestamp,'MM-YYYY') as date and rename the Brand_Type field else Qlikview will create sythetic keys then do your analysis..
Hi Chandra, thanks for the input. Both files are on day level now. These are separated sources. To put them together I would need a generic key or a synthetic primary key that links the two tables together. Can't get this to work, would like some help on creating the appropriate script.
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.
By using just the date, the dimensions in both tables would not be connected right. There is e.g. product group and product name connected to the facts in both tables.
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...):
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.