Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everyone,
I'm having trouble figuring out how to link my tables together so that I can use one filter to select on all data. I have two common fields, School and Gender in both of my main tables and want to be able to make selections / comparisons across all data.
My current data model looks like this:
I have some bookings data that looks like this:
BookingsKey | Enrol ID | Course Code | Student ID | Gender | Date From | Date To | FlagGL | School |
---|---|---|---|---|---|---|---|---|
66146-YLE1 | 66146 | YLE1 | 52003 | M | 06/07/17 | 17/07/17 | 0 | L |
67541-YBE1J | 67541 | YBE1J | 53282 | F | 03/07/17 | 14/07/17 | 0 | B |
67561-YBE1 | 67561 | YBE1 | 53296 | M | 03/07/17 | 14/07/17 | 0 | B |
69338-YLE1 | 69338 | YLE1 | 54953 | M | 06/07/17 | 17/07/17 | 0 | L |
69338-YLE2 | 69338 | YLE2 | 54953 | M | 20/07/17 | 31/07/17 | 0 | L |
75823-YBIAE2 | 75823 | YBIAE2 | 60839 | F | 03/07/17 | 28/07/17 | 0 | B |
74851-YLE1 | 74851 | YLE1 | 60090 | F | 06/07/17 | 17/07/17 | 1 | L |
74306-YBE1 | 74306 | YBE1 | 59594 | M | 03/07/17 | 14/07/17 | 1 | B |
Each School has a fixed capacity per gender which is held in a separate table called YL:
YL.Date | WeekYear | YL.School | YL.Category | YL.Gender | YL.Capacity |
---|---|---|---|---|---|
02/07/2017 | 27-2017 | B | GL Beds | M | 2 |
02/07/2017 | 27-2017 | B | GL Beds | F | 2 |
02/07/2017 | 27-2017 | B | Total Beds | M | 150 |
02/07/2017 | 27-2017 | B | Total Beds | F | 159 |
05/07/2017 | 27-2017 | L | GL Beds | M | 1 |
05/07/2017 | 27-2017 | L | GL Beds | F | 4 |
05/07/2017 | 27-2017 | L | Total Beds | M | 99 |
05/07/2017 | 27-2017 | L | Total Beds | F | 154 |
(The bookings_x_dates table is created by a while loop that generates all the possible dates for each booking using the DateFrom and DateTo fields - )
Ultimately I want to be able to filter on School and Gender to see how many students/GLs we have on any given date/week compared to how many beds are remaining in the school in a table similar to the below:
Obviously one of the problems is that the two bottom rows, no. of GLs and no. of students are not being split by gender because the two tables are not linked properly. If I unqualify the gender and school fields in the YL table though this causes chaos with links and synthetic keys (see below).... How can I get around this problem? Would a link table be the way forward and if so, how would I go about that?
Hi Josephine,
Why does your table YT have dates? If this is a table of fixed capacities then they won't vary over time. Or have I misunderstood?
I think Interval Match is the way to go here. Try this, it won't be the final answer but it might help you along:
Bookings:
LOAD BookingsKey,
[Enrol ID],
[Course Code],
[Student ID],
Gender,
[Date From],
[Date To],
FlagGL,
School
FROM
[https://community.qlik.com/thread/265110]
(html, codepage is 1252, embedded labels, table is @1);
//Get Min From Date
From:
Load
Min(From) as From;
LOAD
FieldValue('Date From', RecNo()) as From
AutoGenerate FieldValueCount ('Date From');
//Get Max To Date
To:
Load
Max(To) as To;
LOAD
FieldValue('Date To', RecNo()) as To
AutoGenerate FieldValueCount ('Date To');
Let vStartDate = Peek('From',0,'From');
Let vEndDate = Peek('To',0,'To');
Drop Tables To, From;
[Calendar]:
Load
Week(Temp_Date) & '-' & Year(Temp_Date) as WeekYear,
Date(Temp_Date,'$(DateFormat)') as Date;
Load $(vStartDate) -1 + IterNo() as Temp_Date
AutoGenerate 1 While $(vStartDate) -1 + IterNo() <= $(vEndDate) ;
MatchTable:
IntervalMatch (Date)
LOAD Distinct [Date From], [Date To] Resident Bookings;
Cheers
Andrew
I am not sure if that helps but you can try like:
Create a key on Gender and School in both the tables like.
YL:
LOAD AutoNumberHash128(Gender&School, 1) AS %Gen_SchoolKey
Gender AS YL_Gender,
School AS Yl_School,
Bookingweekyear AS YL_BookWeekYear,
Category,
Date,
Capacity
FROM YLSource;
Bookings:
LOAD AutoNumberHash128(Gender&School, 1) AS %Gen_SchoolKey
Gender ,
School ,
StudentID....
FROM BookingsTableSource;
Hello Vishwarath,
Thanks so much for your reply. I did as you suggested but it's not quite giving me what I need unfortunately. I'm still not able to compare both sets of data filtered by gender and there is still a loop in my table structure which I think is causing the mystery extra column on my pivot table below... Do you have any other ides?
I mentioned in my previous script that you have to rename the column bookingweekyear else you will have circular reference. like do this:
YL:
LOAD AutoNumberHash128(Gender&School, 1) AS %Gen_SchoolKey
Gender AS YL_Gender,
School AS Yl_School,
Bookingweekyear AS YL_BookWeekYear,
Category,
Date,
Capacity
FROM YLSource;
Ah okay, sorry, I totally missed that. If I rename BookingWeekYear though, then I won't be able to use one date filter to select across all data? I need to be able to use the date filters from the master calendar (e.g. year, quarter, month etc) to filter both the YL and Bookings tables.
Hi Josephine,
After looking at your data model, what I suggest is the YL table concatenated with the Bookings table as a fact table, then get rid of the Booking_x_date table and thus have the master calendar relationship with the new fact table.
Carlos M
Hello Carlos,
Thank you for your reply! That's an interesting approach - I hadn't thought of that. The only problem is that I'm not sure how to handle removing the booking_x_date table as this is there to deal with the interval dates? I used the method that Henric describes in his blog post: Creating Reference Dates for Intervals as I also need to be able to see how many bookings we have on any given date. Since my bookings are based on intervals with a FromDate and a ToDate, I'm not sure how I would link the master calendar to this table without a booking_x_dates table or something similar?
Why cant you use fields from Mastercalendar? Sorry can you elaborate, i did not get you.
Can you share your dashboard app with your expected output?
I think you can use Interval match function to create a new field that will be the link between the new fact table and Master calendar. Does that make sense to you?