Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

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

8 Replies

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

Hi

PFA

R u expecting this?

Not applicable

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

Not applicable

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

Not applicable

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

QlikViewSupportExample.png

Thanks,

Ben

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

HI

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

Not applicable

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

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

Community Browser