Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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..; |
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; |
Any help would be most welcome
Kind Regards,
Ivelin
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
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
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
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
Thanks Stefan, works great!
Regards,
Ivelin