Getting a Single Value from a One to Many Relationship

[#1]

StartDate	HorizonDate	Bookings
07/04/2012	07/01/2012	10
07/04/2012	07/02/2012	11
07/04/2012	07/03/2012	12
07/04/2012	07/04/2012	14

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

[MayilVahanan]

Hi

PFA

R u expecting this?

[#3]

  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

[MayilVahanan]

HI

PFA

[#5]

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?

[MayilVahanan]

HI

PFA

[#7]

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

[MayilVahanan]

HI

Ok. Your wish. or use advanced editor, and attach a file..

[#9]

I got the file attached to the message using the advanced editor (the link for it is very small).