Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
conwayro
Contributor
Contributor

Merging three columns into one on table load

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

8 Replies
neelamsaroha157
Specialist II
Specialist II

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
Champion III
Champion III

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
Creator
Creator

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
Champion III
Champion III

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
Creator
Creator

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
Contributor
Contributor
Author

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.