I'm trying to merge three location columns, (which are loaded into the table as per below):
giving me a table which looks like this:
....into a single location column to just give me something that looks like this:
I'm sure that it's a simple line of script...but I can't seem to get it to work!
You can create another field with the concatenation of these 3 fields like - CallType & Facility & Location. You can use a separator as well if you need - CallType & '-' & Facility & '-' & Location
Try something like
PurgeChar(Location_Gympie & '$$$' & Location_NGH & ' $$$' & Location_SCUH, '$') AS Location
Assuming that the $ character cannot be part of any of those three fields.
We can also do this too right?
Then combining the 3 fields to get a new column (referring to Neelam's expr) Try below:
LOAD *, CallType&'-'&Facility&'-'&Location AS NewFacilityLocation;
Trim(LocationGympie&LocationNGH&LocationSCUH) AS Location
(ooxml, embedded labels, table is Sheet1);
Hospital as Facility
, [Call Type] as CallType
, IF(Hospital = 'Gympie', [Location(Gympie)],
IF(Hospital = 'Nambour', [Location(NGH)],
IF(Hospital = 'SCUH', [Location(SCUH)], 'Unknown'
I see there is a pattern of which column data needs to be selected based on hospital name. May be you can take advantage of that?
You are right, if there are multiple facilities (too many to check) than it would better to create a mapping table for the same and use applymap in load statement.
If you do that, the result will have extra dashes that probably you don't want, for example
My idea with the PurgeChar is adding an extra step to what you are proposing and remove those characters before storing the value into the field.