topic Re: How to compare two dimensions with aggr function in App Development

How to compare two dimensions with aggr function

Anonymous — Mon, 12 Sep 2016 11:31:16 GMT

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:

a) Solving the circular reference by adjusting my data-model
b) Correct the function or replace it somehow.

Thanks!

Re: How to compare two dimensions with aggr function

sunny_talwar — Mon, 12 Sep 2016 15:45:03 GMT

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?

Re: How to compare two dimensions with aggr function

Anonymous — Mon, 12 Sep 2016 19:23:49 GMT

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

Re: How to compare two dimensions with aggr function

sunny_talwar — Tue, 13 Sep 2016 00:01:07 GMT

How about this?

Count(Aggr(If(RoomType_ID = RoomType2_ID, NrOfRooms),RoomType_ID, RoomType2_ID, NrOfRooms))

Re: How to compare two dimensions with aggr function

Anonymous — Tue, 13 Sep 2016 07:10:45 GMT

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!

Re: How to compare two dimensions with aggr function

brunobertels — Tue, 13 Sep 2016 07:48:10 GMT

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

Re: How to compare two dimensions with aggr function

Anonymous — Tue, 13 Sep 2016 12:50:18 GMT

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

Re: How to compare two dimensions with aggr function

sunny_talwar — Wed, 14 Sep 2016 02:10:11 GMT

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)

Re: How to compare two dimensions with aggr function

Anonymous — Wed, 14 Sep 2016 11:26:47 GMT

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!!

Re: How to compare two dimensions with aggr function

sunny_talwar — Thu, 15 Sep 2016 01:06:09 GMT

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

Re: How to compare two dimensions with aggr function

Anonymous — Fri, 16 Sep 2016 08:45:56 GMT

Thanks!