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,
There is a step missing, after doing the interval match you have to join the result with the Bookings table, that way you will avoid the Synthetic table.
Carlos M
Interval Match creates a Synthetic Keys and it is natural. You should be fine having that, and is allowed.
Read here the last two paragraphs.
You can try something like:
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 Max and Min date to create a table with date range
MaxMinDate:
LOAD
Min(num([Date From],'###0')) as MinDate,
Max(num([Date To],'###0')) as MaxDate
Resident Bookings;
LET vMinDate = Peek('MinDate',-1,'MaxMinDate');
LET vMaxDate = Peek('MaxDate',-1,'MaxMinDate');
DROP Table MaxMinDate;
//Create a date range
Dates_Tmp:
LOAD
Date($(vMinDate)+(IterNo()-1),'DD/MM/YYYY') as Date
AutoGenerate(1)
While ($(vMinDate)+(IterNo()-1) <= $(vMaxDate));
//Use interval match to create entry for each date
BookingsTmp2:
IntervalMatch(Date)
LOAD Distinct [Date From], [Date To] Resident Bookings ; //Important to use Distinct clause other wise the data will be multiplied
DROP Table Dates_Tmp;
//Join Dates to Fact table
Left Join(BookingsTmp2)
LOAD *
Resident Bookings;
DROP Table Bookings;
NoConcatenate
Fact:
LOAD
*
Resident BookingsTmp2
Order by Date;
DROP Table BookingsTmp2;
Hmm, I tried the left join but it dramatically increased the number of rows in the bookings table and didn't display the right values in the pivot table. I think I will leave the synthetic key in there for now after reading Henric's post on the topic. Thanks so much for your help everyone, I was really struggling with this one!