How about this for a quick fix
Sum(Aggr(Sum(If([RoomType Id] = [RoomType2Id], [CapacityOfRoomType])),[RoomType Id], [RoomType2Id]))
For a longer term fix may be create a better data model. It would be difficult to propose anything without looking at what you have today. Would you be able to share a sample?
Sunny, Thanks, but it doesn't work. I've included a sample, maybe you can have a look ? Thanks a lot.
And yes, I'm eager to have the longer term fix
How about this?
Count(Aggr(If(RoomType_ID = RoomType2_ID, NrOfRooms),RoomType_ID, RoomType2_ID, NrOfRooms))
It looks allright, but it isn't unfortunately. If you add more rooms, you see that it doesn't work.
And I made a slight error in using Count instead of Sum.
See attached qvf.
LOAD * Inline [Location_ID, RoomType_ID, NrOfRooms
L1, R1, 2
L1, R2, 1
L2, R1, 1
L3, R1, 5
L3, R2, 10
Thanks for your help!
How about this
1) Sum(Aggr(Count(If(RoomType2_ID = RoomType_ID, Appointment_ID)), RoomType2_ID, RoomType_ID, Appointment_ID))
TestApp (1).qvf 192.0 K
Awesome, I am glad I was able to put you in the right direction. Since you have been able to resolve the issue, I would suggest you to mark your response as correct and mark any helpful responses to close down this thread.
The fact you get a circular reférence is not a problem.
have a look here for further info about circular reference
So first i would try with a circular reference
Another way could be to join (leftjoin) the appointement table and the location table
For this sample file, the circular reference is indeed not the problem. Well spotted! But for my actual database, with over 10 tables, it is a problem. So I have to find a way to correct the circular reference, somehow as stated in the excellent example you posted (thanks!).
So indeed I need two keys: RoomType_ID and RoomType2_ID. Now I have to find the correct function to get both results in one table..