8 Replies Latest reply: Nov 28, 2012 4:38 PM by ben.daniel

# Getting a Single Value from a One to Many Relationship

StartDate
HorizonDateBookings
07/04/201207/01/201210
07/04/201207/02/201211
07/04/201207/03/201212
07/04/201207/04/201214

QlikView Community,

I have a case when I am modeling a hotel.  People book up to the date of arrival as shown in the example above.  I need to write an expression that gets the the last line from my table above.  So that I can show the final number of bookings without displaying all the bookings?

Can someone help me write this expression?

Thanks,

Ben

• ###### Re: Getting a Single Value from a One to Many Relationship

Hi

PFA

R u expecting this?

• ###### Re: Getting a Single Value from a One to Many Relationship

Almost.  I think to solve my issue see the data below:

"PROPERTY","STARTDATE","POST_DATE","EVAL_CAP","CURRENT_BKD","BOOKINGS_REC_PRICE","UNCONSTRAINED_DEMAND","SELLOUT_SITUATION"

"ABC",09-NOV-13,24-NOV-12,90,5,6,6,0

"ABC",09-NOV-13,01-DEC-12,90,5,7,7,0

"ABC",09-NOV-13,08-DEC-12,90,5,7,7,0

"ABC",09-NOV-13,15-DEC-12,90,5,8,8,0

"ABC",09-NOV-13,22-DEC-12,90,5,9,9,0

"ABC",09-NOV-13,29-DEC-12,90,5,9,9,0

"ABC",09-NOV-13,05-JAN-13,90,5,10,10,0

"ABC",09-NOV-13,12-JAN-13,90,5,11,11,0

"ABC",09-NOV-13,19-JAN-13,90,5,11,11,0

"ABC",09-NOV-13,26-JAN-13,90,5,12,12,0

"ABC",09-NOV-13,02-FEB-13,90,5,13,13,0

"ABC",09-NOV-13,09-FEB-13,90,5,14,14,0

"ABC",09-NOV-13,16-FEB-13,90,5,15,15,0

"ABC",09-NOV-13,23-FEB-13,90,5,16,16,0

"ABC",09-NOV-13,02-MAR-13,90,5,17,17,0

"ABC",09-NOV-13,09-MAR-13,90,5,18,18,0

"ABC",09-NOV-13,16-MAR-13,90,5,19,19,0

"ABC",09-NOV-13,23-MAR-13,90,5,20,20,0

"ABC",09-NOV-13,30-MAR-13,90,5,21,22,0

"ABC",09-NOV-13,06-APR-13,90,5,23,23,0

"ABC",09-NOV-13,13-APR-13,90,5,24,24,0

"ABC",09-NOV-13,20-APR-13,90,5,25,25,0

"ABC",09-NOV-13,27-APR-13,90,5,26,27,0

"ABC",09-NOV-13,04-MAY-13,90,5,27,28,0

"ABC",09-NOV-13,11-MAY-13,90,5,29,29,0

"ABC",09-NOV-13,18-MAY-13,90,5,30,30,0

"ABC",09-NOV-13,25-MAY-13,90,5,31,32,0

"ABC",09-NOV-13,01-JUN-13,90,5,33,33,0

"ABC",09-NOV-13,08-JUN-13,90,5,34,35,0

"ABC",09-NOV-13,15-JUN-13,90,5,36,37,0

"ABC",09-NOV-13,22-JUN-13,90,5,38,38,0

"ABC",09-NOV-13,29-JUN-13,90,5,40,40,0

"ABC",09-NOV-13,06-JUL-13,90,5,42,42,0

"ABC",09-NOV-13,13-JUL-13,90,5,43,44,0

"ABC",09-NOV-13,20-JUL-13,90,5,46,46,0

"ABC",09-NOV-13,27-JUL-13,90,5,48,48,0

"ABC",09-NOV-13,03-AUG-13,90,5,49,50,0

"ABC",09-NOV-13,10-AUG-13,90,5,51,52,0

"ABC",09-NOV-13,17-AUG-13,90,5,53,54,0

"ABC",09-NOV-13,24-AUG-13,90,5,55,55,0

"ABC",09-NOV-13,31-AUG-13,90,5,56,57,1

"ABC",09-NOV-13,07-SEP-13,90,5,58,59,1

"ABC",09-NOV-13,14-SEP-13,90,5,60,60,1

"ABC",09-NOV-13,21-SEP-13,90,5,61,62,1

"ABC",09-NOV-13,28-SEP-13,90,5,63,64,1

"ABC",09-NOV-13,05-OCT-13,90,5,64,65,1

"ABC",09-NOV-13,12-OCT-13,90,5,65,66,1

"ABC",09-NOV-13,19-OCT-13,90,5,67,68,1

"ABC",09-NOV-13,26-OCT-13,90,5,69,70,1

"ABC",09-NOV-13,02-NOV-13,90,5,70,71,1

"ABC",09-NOV-13,09-NOV-13,90,5,73,75,1

So the single row I'm looking for would read

"PROPERTY","STARTDATE","EVAL_CAP","CURRENT_BKD","BOOKINGS_REC_PRICE","UNCONSTRAINED_DEMAND","SELLOUT_SITUATION"

"ABC",09-NOV-13,90,5,73,75, 11

Take this data set, and what I need is to show the sum of the sellout situation column, but at the same time only showing the final occupancy and unconstrained occupancy.  For some reason, my qlikview report is not showing this unless I explicitly select the final post_date.

Thanks,

Ben

• ###### Re: Getting a Single Value from a One to Many Relationship

HI

PFA

• ###### Re: Getting a Single Value from a One to Many Relationship

Not quite: you have the current bookings summed up rather than the sellout_situation column.  Can you give me the final values for the "PROPERTY","STARTDATE","EVAL_CAP","CURRENT_BKD","BOOKINGS_REC_PRICE","UNCONSTRAINED_DEMAND"

columns, but the aggregate (summed) value for sellout_situation on a single line?

• ###### Re: Getting a Single Value from a One to Many Relationship

HI

PFA

• ###### Re: Getting a Single Value from a One to Many Relationship

This is what I'm looking for (color codes are irrelevant in yoru answer)  I should be able to have a resource ( or property in this case), and then see the Occupancies for each startdate, but the total of each sellout value from all the post_dates leading up to the startdate

Thanks,

Ben

• ###### Re: Getting a Single Value from a One to Many Relationship

HI