Pulling from the same table twice

[raynac]

My database has a reservation table, a client table, and an additional guest table.

A Reservation has a main guest that's attached to the reservation (that guest is stored in the client table) and can have multiple additional guests, each of which is stored in an additional guest table which then joins back to the client table to get that additional guest's address info.

So

     Reservation --> Client

and

     Reservation --> Additional Guest Client Number --> Client

I need to create a mailing list that once reservation selections are made, will show all Clients, be they main or additional.  I tried concatenating two instances of the client table in the script, but it gives me the same result as if I had just tried to join back to the table a second time..."One or More Ambiguous loops...etc".

Is this even possible?  Or will I have to just have a mailing list for main guests and a separate one for additional guests?

[Anonymous]

Are you able to post an example? How many fields are you actually using to do this? If you're concatenating the same data, you just end up duplicating your data if it is the exact same rows.

[Anonymous]

do you have one table from which you are pulling the data or two? what is your table structure for the table(s)?

if your 2nd table already has client information and the additional guest client info, then you may not have to load the same field twice in two different tables. you obviously need to load your reservation id in both the tables to associate the data in the two tables.

can you post a sample of your data here? probably easier that way than trying to troubleshoot what you may have done or could do.

[Colin-Albert · Accepted solution]

A simple fix is to load the address data twice. Once into a Client table, and again into an AdditionalGuest table.

The filed names in these tables must be unique, otherwise you will get a loop in your data.

e.g.

ClientAddress:

Load

ClientID,

Address,

City,

Country

from AddressData ;

AdditionalGuestAddress:

Load

ClientID as GuestID,

Address as GuestAddress     ,

City           as GuestCity,

Country as GuestCountry

from AddressData

A more comprehensive fix would be to change your data so the ClientID and GuestID are loaded into a Reservation /  link table. This links the single reservation to multiple guests, you could include another field to identify the primary contact and additional guests, this way all guests link via one address table.

[raynac]

Agreed!  But since a person could technically be both a main client and an additional guest, they need to be both connected directly to the client table and connected through the additional guest table.

Let me see if I can post the view here.  It's an awful lot of data, though!

[Colin-Albert]

You can reduce the data in QlikView to load a small sample and scramble the data to anonymise any values.

For details look at this post   http://community.qlik.com/docs/DOC-1290

[raynac]

Thanks, Colin!  The two different tables is the obvious and easy way, for sure 🙂  That was the way I did it first.  But then if I wanted a single email list for export, this method won't get me there.  I need one for each dataset.

I love the idea of a "bridge" table (I have built those before in other views but didn't think of it here...)  I will give that a try.

[rwunderlich]

You may also consider creating a row in the Reservations table with ClientId  for every guest, be they primary or additional.

-Rob

[raynac]

Thank you Colin...I built that linking table and it worked like a charm!!

[raynac]

I found a solution, but out of curiousity, would that not cause duplication of data?

I suppose if you constantly counted distinct(reservation number) I guess that would keep it from being an issue.