Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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

View solution in original post

4 Replies
swuehl
MVP
MVP

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

Not applicable
Author

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

swuehl
MVP
MVP

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

Not applicable
Author

Thanks Stefan, works great!

Regards,

Ivelin