topic Getting a Single Value from a One to Many Relationship in QlikView

Getting a Single Value from a One to Many Relationship

Wed, 28 Nov 2012 19:15:31 GMT

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

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

MayilVahanan — Wed, 28 Nov 2012 19:26:29 GMT

PFA

R u expecting this?

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

Wed, 28 Nov 2012 20:10:59 GMT

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

MayilVahanan — Wed, 28 Nov 2012 20:28:33 GMT

PFA

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

Wed, 28 Nov 2012 20:43:48 GMT

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

MayilVahanan — Wed, 28 Nov 2012 20:54:52 GMT

PFA

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

Wed, 28 Nov 2012 21:33:54 GMT

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

MayilVahanan — Wed, 28 Nov 2012 21:37:22 GMT

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

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

Wed, 28 Nov 2012 21:38:29 GMT

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