topic Re: Preventing duplicated data in summaries in QlikView

Preventing duplicated data in summaries

raynac — Wed, 03 Jun 2015 17:42:13 GMT

I am hoping that someone here can assist with this. I have two tables that need to join together based on building code and date. I do that in the script.

One of them has a record of available rooms per day and building; the second has many records (with fields that include building code and date) that I add up to get rooms occupied. One of these summaries is by guest type, so I will use that as my example, but I summarize by other fields as well.

Here is the main problem: if I try and sum() the available rooms, I get a record for each guest type. So if I have 150 rooms but 4 guest types, my chart says I have 600 rooms. I can use max(), but then when I try and do this for a week and need, say 1050 (150 X 7) rooms, the max produces just the 150, which is totally incorrect.

I am convinced there has to be a reasonable way to get 150 rooms per day, period, and have it summarize by week, month etc as needed. I tried AGGR:

=aggr(sum([Rooms]), building_code, [Date])

But that gives me the same issue so either I have chosen the wrong function or I am doing it incorrectly.

Does anyone have any insight they can offer?

Thank you!

Re: Preventing duplicated data in summaries

Clever_Anjos — Wed, 03 Jun 2015 18:02:54 GMT

you should sum your aggr

=SUM(aggr(sum([Rooms]), building_code, [Date]))

Re: Preventing duplicated data in summaries

raynac — Wed, 03 Jun 2015 18:14:26 GMT

Hi there,

Thanks so much for your message! Unfortunately, that didn't make any difference to the chart. Still getting multiple records per date per building.

Re: Preventing duplicated data in summaries

Colin-Albert — Wed, 03 Jun 2015 18:19:33 GMT

Can you post an image of your data model from the Table Viewer, so we can see how you tables are associated?

Re: Preventing duplicated data in summaries

raynac — Wed, 03 Jun 2015 18:56:40 GMT

Hi Colin,

The "ResRevJoin" is a concatenation of the building_code and Stats Date to avoid synthetic keys. fdpropsumm is the summary with a row per date per building. And fdresrev is the table that contains a record per reservation per day, and the reservations can only have one guest type each but there can be a myriad of the codes on any given day.

Does this help?