Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
raynac
Partner - Creator
Partner - Creator

Eradicating Duplicate Records

My database has two tables:

1) "reservation revenue" contains a record per day per individual room reservation in the system, and has many fields including a date, a building code and a field called number_of_nights 

2) "building summary" contains a record per building per day that holds the number of available rooms.

If I join the two tables based on building code and date, and then Sum() the number of available rooms in a chart, the number is totally skewed as it appears to be picking up a record in "building summary" for each record in "reservation revenue", which is not an unreasonable thing to expect.  If I Max() the available rooms instead, a day by day breakdown works perfectly, but then they won't sum up when I roll it up for, say, a year at a time.

In addition, all other individual fields in the reservation revenue table must be accessible as selectors, so I believe this precludes me from building a version of that table in the script with a group by clause.

So my question is this:  how do I get a chart where I get the proper sum of room nights, and the proper number of available rooms so I can calculate an occupancy percentage that will still be able to roll up by week/month/year etc.

I'd appreciate any help anyone can provide.  I am convinced it can be done but that I am just not seeing it.

18 Replies
swuehl
MVP
MVP

Maybe by not joining the tables. Consider concatenating the two tables instead or just leave them linked by a key built out of date and building.

raynac
Partner - Creator
Partner - Creator
Author

Hi there!  Thank you for your response.

I am not sure how you'd concatenate the two tables...my understanding was that concatenation takes all the rows from one table and appends the rows from a second table to it.  Have I misunderstood?

If you create a key built from the date and building, won't that still result in duplicating the records in building summary?

Peter_Cammaert
Partner - Champion III
Partner - Champion III

What do you mean by "join the two tables..."?

Keeping your two tables separate in your data model would IMHO fix most of the problems you mention, but that would depend on how you create your objects and charts.

It would be very helpful to us if you could post your document (optionally with example data instead of the real stuff) in this thread.

Best,

Peter

Not applicable

Is the building summary table the aggregation of the inforamtion in the reservation revenue?  If it is, can you just bring in the reservation revenue table and allow QlikView to do the aggregation - this will then have a single source of the detail and aggregation and will tie the selection criteria to be associated to both

Peter_Cammaert
Partner - Champion III
Partner - Champion III

Associations work like real script JOINs, except that in a grand total they count every summary record only once.

raynac
Partner - Creator
Partner - Creator
Author

Hi Peter,

Interesting.  But if you keep the two tables separate in the data model, how would that even work?

I always understood that tables needed to be "joined" (maybe I am using non-standard language?) by primary and foreign keys.  So if I don't use the building code and date to connect the two tables together, then how would they connect? 

I am mostly self taught with Qlikview so perhaps there's a whole world of stuff I don't know how to do with the program.

Thanks for your help!

Rayna

swuehl
MVP
MVP

If you create a key built from the date and building, won't that still result in duplicating the records in building summary?

Not if the fields used for aggregation in the front end chart belongs to the same internal data model table (i.e. there is no need for QlikView to perform the actual join of the two tables on runtime).

raynac
Partner - Creator
Partner - Creator
Author

Hi Adam,

Sadly no.  The reservation revenue table contains reservations made by guests.  The building summary is "how many rooms do we have available to sell?".  So I use the number of rooms booked per given day, divide that by the number of rooms available to sell on that day to get an occupancy percentage.  Most clients need that percentage by month or year, not really by day.  So I have to be able to "roll up" the calculation.

raynac
Partner - Creator
Partner - Creator
Author

I have a Qlikview reference book and looked up associations.  The syntax has a similar structure to Set Analysis, correct?  (or maybe it is Set Analysis?)