4 Replies Latest reply: Oct 30, 2012 10:37 AM by Ivelin Iliev RSS

    Date from multiple tables and loosely coupled tables

      Hi,

       

      That’s my first QlikView project and I’m facing a problem I’ve been struggling with for quite some time.

       

      I have data from 2 databases. They contain ID1/Date/Downloads and ID2/Date/ Visitors, respectively. Date is the same for both databases and it is in a correct date format. ID1 and ID2 are different but they correspond to the same title. That’s why I created an extra excel file with matching ID1/ID2/Title. I want to create a QlikView dashboard with Title/ID1/ID2/Downloads/Visitors all together in a Straight table box and a separate Date box, so that I can select a specific Date and see the Downloads and Visitors per Title on that date.

       

      If I simply link the fields in the way I need them I end up with loosely coupled tables:

      Script 1
      TableView 1

      Database1:

      LOAD ID1, Date1 as Date, Downloads

      FROM..;

       

      Database2:

      LOAD ID2, Date2 as Date, Visitors

      FROM..;

       

      Local:

      LOAD ID1, ID2, Title

      FROM..;

      export1.png

       

      I added a MasterCalendar and Link table as suggested by John Witherspoon in another post. This solves the problem with the dates but the loose coupling remains.

      Script 2TableView 1

      Database1:

      Load *,

      AutoNumber(Date1) as Key1;

      LOAD ID1, Date1, Downloads

      FROM..;

       

      Database2:

      Load *,

      AutoNumber(Date2) as Key2;

      LOAD ID2, Date2, Visitors

      FROM..;

       

      Local:

      LOAD ID1, ID2, Title

      FROM..;

       

      LinkTable:

      Load

      Date1 as Date,

      Key1

      resident Database1;

       

      Concatenate(LinkTable)

      Load

      Date2 as Date,

      Key2

      resident Database2;

       

      Calendar:

      LOAD *

      ,date(monthstart(Date),'MMM') as Month

      ,date(yearstart(Date),'YYYY') as Year;

      LOAD date(makedate(2012,10,1)+recno()-1) as Date

      AUTOGENERATE 365;

      export.png

       

       

      Any help would be most welcome

       

      Kind Regards,

      Ivelin

        • Re: Date from multiple tables and loosely coupled tables
          Stefan Wühl

          Try replacing your ID2 with ID1 values, using a mapping table based on your local table. Then you can concatenate your fact tables coming from DB1 and DB2, having common fields ID1 and Date, and fields Downloads and Visitors partially filled. You can keep your Title table linked to your facts via ID1 or just join it / map it to you fact table.

           

          You can create a master calendar table and link it to Date, if you want.

           

          I think this should solve your issues.

           

          Regards,

          Stefan