Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
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

Highlighted

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.

Highlighted
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);

Highlighted
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.

Highlighted
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.

Highlighted
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.

Highlighted
Contributor
Contributor

Hi all,

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

Highlighted

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.