Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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"
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"
.......
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?
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"
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?
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"
.......
Thanks Massimo! That worked! 😄