Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
raynac
New Contributor III

Connecting two fields to one field

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"

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: Connecting two fields to one field

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"

    .......

5 Replies
raynac
New Contributor III

Re: Connecting two fields to one field

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

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"

raynac
New Contributor III

Re: Connecting two fields to one field

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?

MVP
MVP

Re: Connecting two fields to one field

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"

    .......

raynac
New Contributor III

Re: Connecting two fields to one field

Thanks Massimo!  That worked! :-D

Community Browser