topic Re: Merging three columns into one on table load in QlikView

Merging three columns into one on table load

conwayro — Thu, 13 Sep 2018 03:28:33 GMT

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

Re: Merging three columns into one on table load

neelamsaroha157 — Thu, 13 Sep 2018 14:26:51 GMT

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

Re: Merging three columns into one on table load

Miguel_Angel_Baeyens — Thu, 13 Sep 2018 15:03:20 GMT

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.

Re: Merging three columns into one on table load

vishsaggi — Thu, 13 Sep 2018 15:17:25 GMT

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

Re: Merging three columns into one on table load

rajivmeher — Thu, 13 Sep 2018 17:11:44 GMT

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.

Re: Merging three columns into one on table load

vishsaggi — Thu, 13 Sep 2018 17:25:04 GMT

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.

Re: Merging three columns into one on table load

rajivmeher — Thu, 13 Sep 2018 17:35:20 GMT

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.

Re: Merging three columns into one on table load

conwayro — Fri, 14 Sep 2018 01:02:15 GMT

Hi all,

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

Re: Merging three columns into one on table load

Miguel_Angel_Baeyens — Fri, 14 Sep 2018 08:04:00 GMT

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.