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: 
Anonymous
Not applicable

Help with common fields and synthetic keys

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:

TableStructure.JPG

I have some bookings data that looks like this:

BookingsKeyEnrol IDCourse CodeStudent IDGenderDate FromDate ToFlagGLSchool
66146-YLE166146YLE152003M06/07/1717/07/170L
67541-YBE1J67541YBE1J53282F03/07/1714/07/170B
67561-YBE167561YBE153296M03/07/1714/07/170B
69338-YLE169338YLE154953M06/07/1717/07/170L
69338-YLE269338YLE254953M20/07/1731/07/170L
75823-YBIAE275823YBIAE260839F03/07/1728/07/170B
74851-YLE174851YLE160090F06/07/1717/07/171L
74306-YBE174306YBE159594M03/07/1714/07/171B

Each School has a fixed capacity per gender which is held in a separate table called YL:

YL.DateWeekYearYL.SchoolYL.CategoryYL.GenderYL.Capacity
02/07/201727-2017BGL BedsM2
02/07/201727-2017BGL BedsF2
02/07/201727-2017BTotal BedsM150
02/07/201727-2017BTotal BedsF159
05/07/201727-2017LGL BedsM1
05/07/201727-2017LGL BedsF4
05/07/201727-2017LTotal BedsM99
05/07/201727-2017LTotal BedsF154

(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:

CapacitiesTable.JPG

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?

Synthetic Key and Loops.JPG

23 Replies
Anonymous
Not applicable
Author

Hmm, I'm not too familiar with that function (hence why I went for the extra table option!) but I will do some investigating and see if it works. Thanks for the tip!

Anonymous
Not applicable
Author

I would love to share the app but at the moment, it has all the database connections / file locations etc in it so it will take me some time to redo it and load the data from QVDs instead... Otherwise I will probably get sacked for violating my company's security policy! 🙂

Anonymous
Not applicable
Author

If I break the link between the YL table and the master calnedar (via BookingWeekYear), I end up with this:

TableStructure2.JPG

But unfortunately it messes up the data - my capacities table now looks like this:

CapacitiesTable3.JPG

The YL data (Total Beds / GL beds) is no longer summed by weekyear since the date link has been broken so the figures are much bigger than they should be.

I actually need this table to show these figures based on the data that I have:

WeekYear27-201727-2017
YL.GenderMF
Total Beds249313
GL Beds36
GLs?311
Students?188242
vishsaggi
Champion III
Champion III

No worries. But did you try changing that field name and whats your data model now looks like?

You can scramble the confidential data using scrambling technique. follow here:

Preparing examples for Upload - Reduction and Data Scrambling

effinty2112
Master
Master

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

vishsaggi
Champion III
Champion III

What are your expressions for Total Beds and GL beds? Can you share if possible?

Anonymous
Not applicable
Author

Hey Andrew,

The YL table has dates because the capacities can vary each week depending on how many beds we've booked / how many beds are available at each school.

This is brilliant - thank you! I'll give it a go and see how I get on.

Anonymous
Not applicable
Author

Ok, here's the app. I've taken out the connections and reverted back to the previous way of linking the tables. I'm going to have a go at concatenation / interval match but any help in the meantime would be much appreciated! 🙂

vishsaggi
Champion III
Champion III

I somehow feel YL should not be joining with Master calendar. Probably give it a try using Interval Match or Concatenating Bookings and YL tables. Cos your are already getting BookingsWeekYear from your Calendar. And what data holds YL table and how that is related to Bookings table? Can you elaborate  a little please.

Anonymous
Not applicable
Author

I concatenated the capacities data and then used interval match but I've ended up with a synthetic key... Is this okay/expected? I don't think it has affected the bookings data - the row count is as expected and even better, the capacities pivot table is now displaying all the data split by gender! 😄

TableStructure3.JPG

CapacitiesTable4.JPG