8 Replies Latest reply: Dec 13, 2012 9:08 AM by dasbmx32 RSS

    Combine or link two data sources

      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!

        • Re: Combine or link two data sources
          Chandrasekhar Reddy

          Hi,

           

          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..

           

          Regards,

          Chandra

            • Re: Combine or link two data sources

              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.

                • Re: Combine or link two data sources
                  sujeet singh

                  Why not to link them on date

                   

                  ???

                  • Re: Combine or link two data sources
                    Jonathan Dienst

                    Hi

                     

                    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.

                     

                    Regards

                    Jonathan

                      • Re: Combine or link two data sources

                        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.

                          • Re: Combine or link two data sources
                            Jonathan Dienst

                            Hi

                             

                            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:

                             

                            ProductGroups:

                            LOAD Distinct unique_name,

                                 Productgroup

                            Resident dataset2

                             

                            ...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.

                             

                            Regards

                            Jonathan

                              • Re: Combine or link two data sources

                                Hi Jonathan,

                                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.