Merging three columns into one on table load

[conwayro]

Hi,

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!

TIA

Roger

[neelamsaroha157]

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

[Miguel_Angel_Baeyens]

Try something like

LOAD

...

PurgeChar(Location_Gympie & '$$$' & Location_NGH & ' $$$' & Location_SCUH, '$') AS Location

...

Assuming that the $ character cannot be part of any of those three fields.

[vishsaggi]

Hi Miguel,

We can also do this too right?

Then combining the 3 fields to get a new column (referring to Neelam's expr) Try below:

Tab1:

LOAD *, CallType&'-'&Facility&'-'&Location AS NewFacilityLocation;

LOAD CallType,

     Facility,

     Trim(LocationGympie&LocationNGH&LocationSCUH) AS Location

FROM

(ooxml, embedded labels, table is Sheet1);

[rajivmeher]

Hi conwayro‌

Please try:

TableMETCalls:

LOAD

Hospital as Facility

, [Call Type] as CallType

, IF(Hospital = 'Gympie', [Location(Gympie)],

IF(Hospital = 'Nambour', [Location(NGH)],

IF(Hospital = 'SCUH', [Location(SCUH)], 'Unknown'

)

)

)

AS Location

FROM ....................

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?

Regards

Rajiv.

[vishsaggi]

Hi rajiv,

The data he provided was limited what if there are multiple facilities, in such cases you have to write big if statements for each facility name right.

[rajivmeher]

Hi vishsaggi‌

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.

Regards

Rajiv.

[conwayro]

Hi all,

Thank you for the input...the concatenate suggestion worked a treat!!

[Miguel_Angel_Baeyens]

If you do that, the result will have extra dashes that probably you don't want, for example

ASU--

--3A

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.