Eradicating Duplicate Records

[raynac]

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.

[Peter_Cammaert]

We always start by thinking that we misunderstood your case instead of the other way around That's why I asked for an example document so that we can investigate, learn and - hopefully - provide some useful feedback.

The QV associative mode is not an RDBMS. It works in a quite different way. In QlikView a JOIN usually means something like a SQL JOIN whereby in the load script you throw together two tables and hope they'll have as many matching rows as possible. That logic equally applies to a QV associative model, but in a dynamic non-final way. The two tables can stay separate, but as Stefan says you'll need a key to connect them.

When you build an object on top of this data model, you can shoiw all individual reservations on a specific day, together with the corresponding summary. The summary details may seem to be duplicated in each individual row, but the summary totals and the bottom or the top of the object will include each individual summary line only once.

Peter

[raynac]

First off, thank you so much for your patience with me.  As I say, it's likely I go about some of this in a different manner because most of what I know I learned on my own.

"In QlikView a JOIN usually means something like a SQL JOIN whereby in the load script you throw together two tables and hope they'll have as many matching rows as possible."

Yep...that's pretty much the model under which I was operating!  I was joining the two tables together by date and building code, but multiple records in one meant duplicated records in the other.  I expect the Association you mentioned above might be my saving grace, if I can only figure out how to use it.

[Peter_Cammaert]

No, with associations we refer to the mechanism that the QlikView associative database uses to dynamically connect internal tables and walk from selected values in one internal table to the associated rows and fields in another internal table.

[swuehl]

As a first step, remove the JOIN prefix from your LOAD in the script. This should link the two tables by Date and Code by creating a synthetic key. For now, you can leave the synthetic key and check if the data model fit your requirements by building your charts.

[raynac]

And in my case, that sounds like it should work because the lack of join means lack of duplication, correct?

[Peter_Cammaert]

Well, do as Stefan says. Load the first table and create a key from building code and date. You can use something like this:

:

[Building Code] & '|' & Date AS %Key,

:

Then load the second table (do NOT join it to the first) and create the same key. Omit the [Building Code] and Date fields in the second table, as they will be creating synthetic keys. Now reload your document. Do not change anything in the UI, just watch what happens.

You could also jump the gun and load the two tables without creating keys. If building code and date have the same names (identical spelling) in both tables, synthetic keys are created that produce the same behavior as a single key..

Best,

Peter

[Edit] Sorry, started writing before Stefan posted his reply.

[raynac]

HI Stefan,

Between you and pcammaert, that made a huge difference....thank you!  Now, the only issue is that I need all records from building summary, even when there are no reservations that day.  Monthly or annual occupancy has to include all days available rooms.  I think that's why I tried the left outer join to begin with....it drops the days with no reservations.

I am going to attach the model I am using.  If I can figure out how....

[raynac]

Here are the two tables in the app that are causing me grief.

I expect I am making a simple mistake, and I am just not seeing it!

[raynac]

Hi Peter,

I did upload the view a few entries above, but now I have an issue where I am dropping all the records in the summary table that have no reservations in the reservation table.  (I think that was the point of the Left Outer Join to begin with.)  Do you have any ideas on how to make sure I get all the building summary records, regardless of "matches" in the other table?