topic Re: Connecting two fields to one field in QlikView

Connecting two fields to one field

raynac — Thu, 25 Jun 2015 15:53:28 GMT

Here is my dilemma. I have a booking table:

scbooking:

LOAD

"booking_number" as "SC Booking Number",

"client_code" as "Booking Client Code",

description as "SC Booking Description",

"reservation_number" as "FD Group Master",

SQL SELECT *

FROM scbooking where reservation_number>0;

This table connects to a folio header, and can connect via *either* the booking number or the group master reservation number.

I wrote this but the resultant set is disconnected from the scbooking table above.

sourceID:

load [SC Booking Number] as source_id,

"SC Booking Number"

Resident scbooking;

Concatenate(sourceID)

load [FD Group Master] as source_id,

"SC Booking Number"

Resident scbooking;

sc_gbfol_head:

load

"folio_number" as "SC Folio Number",

"source_id"

Re: Connecting two fields to one field

raynac — Thu, 25 Jun 2015 15:56:50 GMT

OK...for some reason I cannot edit or delete the original post. What I WANTED to say was that it's connected but only via the booking number and is still not picking up the folios connected to the reservation number. If I connect via both, that gives me a blank set.

Can anyone see where I am going wrong?

Re: Connecting two fields to one field

Anonymous — Thu, 25 Jun 2015 16:13:01 GMT

A bit of guess without data to test against, but how about something like this ?

scbooking:

LOAD

"booking_number" as "source_id",

"booking_number" as "SC Booking Number",

"client_code" as "Booking Client Code",

description as "SC Booking Description",

"reservation_number" as "FD Group Master",

SQL SELECT *

FROM scbooking where reservation_number>0;

sc_gbfol_head:

load

"folio_number" as "SC Folio Number",

"source_id"

Re: Connecting two fields to one field

raynac — Thu, 25 Jun 2015 16:18:20 GMT

Hi Bill,

Thank you so much for taking the time to respond.

Unfortunately, the reason that won't work is because the source_id can be either the booking_number or the reservation_number. So joining based on one drops all the folios where the source_id matches the other. That was the reason for my attempt to build some sort of "connector" table that would gather up all the possibilities for the source_id while still joining them back to the original booking table.

Does that make sense?

Re: Connecting two fields to one field

maxgro — Thu, 25 Jun 2015 18:09:58 GMT

hope to understand the question (if not, please post some data)

scbooking:

LOAD

rowno() as id,

"booking_number" as "SC Booking Number",

"client_code" as "Booking Client Code",

description as "SC Booking Description",

"reservation_number" as "FD Group Master",

SQL SELECT *

FROM scbooking where reservation_number>0;

linktable:

load id, "SC Booking Number" as joinfield resident scbooking;

concatenate (linktable) load id, "FD Group Master" as joinfield resident scbooking;

sc_gbfol_head:

load

"folio_number" as joinfield,

"source_id"

.......

Re: Connecting two fields to one field

raynac — Thu, 25 Jun 2015 18:31:56 GMT

Thanks Massimo! That worked! 😄