topic Date from multiple tables and loosely coupled tables in QlikView

Date from multiple tables and loosely coupled tables

Tue, 30 Oct 2012 10:15:43 GMT

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

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

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 2	TableView 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;

Script 2

TableView 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;

Any help would be most welcome

Kind Regards,

Ivelin

Re: Date from multiple tables and loosely coupled tables

swuehl — Tue, 30 Oct 2012 11:00:34 GMT

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

Re: Date from multiple tables and loosely coupled tables

Tue, 30 Oct 2012 13:47:22 GMT

Hi Stefan,

although most Titles have both ID1 and ID2, some do not have ID1 and others ID2. I read that 2 columns must be used with map. Below is how the Local.xlsx file looks. I initially added a 3rd ID trying to avoid this problem before the data is loaded in QV but it never came into use. Is there any way around this?

Title ID1 ID2 ID3

Title1 001 102 1

Title2 022 104 2

Title3 105 3

Title4 034 4

Regards,

Ivelin

Re: Date from multiple tables and loosely coupled tables

swuehl — Tue, 30 Oct 2012 14:18:12 GMT

Ok, so I would suggest using two mapping tables to replace ID1 and ID2 with ID3:

Map1:

MAPPING LOAD ID1, ID3 from ...; // Local

Map2:

MAPPING LOAD ID2, ID3 from ....; // Local

Database1:

LOAD

applymap('Map1',ID1,'NoMapping') as ID, Date1 as Date, Downloads

FROM..;

Database2:

CONCATENATE LOAD

applymap('Map2',ID2,'NoMapping') as ID, Date2 as Date, Visitors

FROM..;

Local:

LOAD ID3 as ID, Title

FROM..;

Untested, so beware of typos...

Stefan

Re: Date from multiple tables and loosely coupled tables

Tue, 30 Oct 2012 14:37:07 GMT

Thanks Stefan, works great!

Regards,

Ivelin