topic Re: Eradicating Duplicate Records in QlikView

Eradicating Duplicate Records

raynac — Tue, 29 Sep 2015 15:53:41 GMT

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.

Re: Eradicating Duplicate Records

swuehl — Tue, 29 Sep 2015 15:59:27 GMT

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.

Re: Eradicating Duplicate Records

raynac — Tue, 29 Sep 2015 16:05:02 GMT

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?

Re: Eradicating Duplicate Records

Peter_Cammaert — Tue, 29 Sep 2015 16:06:01 GMT

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

Re: Eradicating Duplicate Records

Tue, 29 Sep 2015 16:11:57 GMT

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

Re: Eradicating Duplicate Records

Peter_Cammaert — Tue, 29 Sep 2015 16:12:07 GMT

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

Re: Eradicating Duplicate Records

raynac — Tue, 29 Sep 2015 16:14:34 GMT

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

Re: Eradicating Duplicate Records

swuehl — Tue, 29 Sep 2015 16:15:42 GMT

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).

Re: Eradicating Duplicate Records

raynac — Tue, 29 Sep 2015 16:16:58 GMT

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.

Re: Eradicating Duplicate Records

raynac — Tue, 29 Sep 2015 16:25:57 GMT

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?)

Re: Eradicating Duplicate Records

Peter_Cammaert — Tue, 29 Sep 2015 16:27:31 GMT

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

Re: Eradicating Duplicate Records

raynac — Tue, 29 Sep 2015 16:47:58 GMT

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.

Re: Eradicating Duplicate Records

Peter_Cammaert — Tue, 29 Sep 2015 16:49:44 GMT

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.

Re: Eradicating Duplicate Records

swuehl — Tue, 29 Sep 2015 16:51:53 GMT

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.

Re: Eradicating Duplicate Records

raynac — Tue, 29 Sep 2015 16:52:16 GMT

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

Re: Eradicating Duplicate Records

Peter_Cammaert — Tue, 29 Sep 2015 16:56:58 GMT

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.

Re: Eradicating Duplicate Records

raynac — Tue, 29 Sep 2015 17:18:42 GMT

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

Re: Eradicating Duplicate Records

raynac — Tue, 29 Sep 2015 17:28:33 GMT

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!

Re: Eradicating Duplicate Records

raynac — Wed, 30 Sep 2015 13:37:43 GMT

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?