topic Re: Help with common fields and synthetic keys in QlikView

Help with common fields and synthetic keys

Anonymous — Thu, 22 Jun 2017 14:31:18 GMT

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?

Re: Help with common fields and synthetic keys

vishsaggi — Thu, 22 Jun 2017 14:48:01 GMT

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;

Re: Help with common fields and synthetic keys

Anonymous — Thu, 22 Jun 2017 15:07:12 GMT

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?

Re: Help with common fields and synthetic keys

vishsaggi — Thu, 22 Jun 2017 15:15:42 GMT

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;

Re: Help with common fields and synthetic keys

Anonymous — Thu, 22 Jun 2017 15:19:49 GMT

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.

Re: Help with common fields and synthetic keys

CarlosAMonroy — Thu, 22 Jun 2017 15:21:42 GMT

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

Re: Help with common fields and synthetic keys

Anonymous — Thu, 22 Jun 2017 15:29:57 GMT

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?

Re: Help with common fields and synthetic keys

vishsaggi — Thu, 22 Jun 2017 15:32:32 GMT

Why cant you use fields from Mastercalendar? Sorry can you elaborate, i did not get you.

Re: Help with common fields and synthetic keys

vishsaggi — Thu, 22 Jun 2017 15:33:42 GMT

Can you share your dashboard app with your expected output?

Re: Help with common fields and synthetic keys

CarlosAMonroy — Thu, 22 Jun 2017 15:35:48 GMT

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?

Re: Help with common fields and synthetic keys

Anonymous — Thu, 22 Jun 2017 15:44:53 GMT

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!

Re: Help with common fields and synthetic keys

Anonymous — Thu, 22 Jun 2017 15:52:03 GMT

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! 🙂

Re: Help with common fields and synthetic keys

Anonymous — Thu, 22 Jun 2017 16:09:09 GMT

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

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

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:

WeekYear	27-2017	27-2017
YL.Gender	M	F
Total Beds	249	313
GL Beds	3	6
GLs?	3	11
Students?	188	242

Re: Help with common fields and synthetic keys

vishsaggi — Thu, 22 Jun 2017 16:10:24 GMT

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

Re: Help with common fields and synthetic keys

effinty2112 — Thu, 22 Jun 2017 16:35:48 GMT

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

Re: Help with common fields and synthetic keys

vishsaggi — Thu, 22 Jun 2017 16:41:29 GMT

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

Re: Help with common fields and synthetic keys

Anonymous — Fri, 23 Jun 2017 11:15:54 GMT

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.

Re: Help with common fields and synthetic keys

Anonymous — Fri, 23 Jun 2017 11:31:56 GMT

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! 🙂

Re: Help with common fields and synthetic keys

vishsaggi — Fri, 23 Jun 2017 11:56:46 GMT

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.

Re: Help with common fields and synthetic keys

Anonymous — Fri, 23 Jun 2017 12:47:53 GMT

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! 😄