5 Replies Latest reply: Jun 25, 2015 2:31 PM by Rayna Curtis RSS

    Connecting two fields to one field

    Rayna Curtis

      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
          Rayna Curtis

          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
              Bill Markham

              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
                  Rayna Curtis

                  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
                Massimo Grossi

                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"

                    .......