Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi there,
I need some help..
I’ve got an database, where appointments of several types are made on multiple locations.
Each appointment type can require different type of rooms (even multiple rooms)
Each location has several rooms of each type.
So kind of like this (oversimplified):
AppointmentsTable:
-AppID
-AppTypeID
-LocationID
RoomType:
-RoomTypeID
-AppTypeID
Location:
-LocationID
-RoomTypeID
-CapacityOfRoomType
Now: If I load this in QV, I get a circular reference of course. So, I’ve renamed the ID of RoomType to RoomTypeID2 – which worked obviously.
But the problem is I can’t get the query right where I want to show the used Rooms vs the capacity.
I want to show:
RoomTypeID – Count(Appointments) – Count(Capacity)
A1 – 45 – 100
A2 – 50 – 102
And now I don’t know to solve it. Any help?
I’ve tried a aggr function like
SUM(aggr(SUM([CapacityOfRoomType]),[RoomType Id], [RoomType2Id])). But it doesn’t work.
I get:
RoomTypeID2 – Count(Appointments) – Count(Capacity)
A1 – 45 – 202
A2 – 50 – 202
If I include RoomTypeID then it shows all the correct values, So I should be close…
RoomType2ID – RoomTypeID - Count(Appointments) – Count(Capacity)
A1 – A1 – 45 – 100
A1 – A2 – 0 – 102
A2 – A1 – 50 – 100
A2 – A2 – 0 – 102
I tried something like:
SUM(${<[RoomType2Id]]=[RoomType Id]>}aggr(SUM(${<[RoomType2 Id]]=[RoomType Id]>}[CapacityOfRoomType]),[RoomType Id], [RoomType2Id])). But it doesn’t work.
I hope someone can help me by:
Thanks!
Sunny,
It works for the sample database, but not for my production one. But, I switched the Count and If statement, and now it works for my database as well:
Sum(Aggr(If(RoomType2_ID = RoomType_ID, Count(Appointment_ID)), RoomType2_ID, RoomType_ID, Appointment_ID))
So thanks a lot!!
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.
----
LocationRooms:
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!
Hi
The fact you get a circular reférence is not a problem.
have a look here for further info about circular reference
https://community.qlik.com/blogs/qlikviewdesignblog/2013/06/25/circular-references
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..
Thanks
How about this
Dimension
RoomType_ID
Expressions
1) Sum(Aggr(Count(If(RoomType2_ID = RoomType_ID, Appointment_ID)), RoomType2_ID, RoomType_ID, Appointment_ID))
2) Sum(NrOfRooms)
Sunny,
It works for the sample database, but not for my production one. But, I switched the Count and If statement, and now it works for my database as well:
Sum(Aggr(If(RoomType2_ID = RoomType_ID, Count(Appointment_ID)), RoomType2_ID, RoomType_ID, Appointment_ID))
So thanks a lot!!
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.
Qlik Community Tip: Marking Replies as Correct or Helpful
Best,
Sunny