LOAD multiple items from one field into a single field

[#1]

Hi,

I have a field that has the form:

Keyword; another keyword; a different keyword; new keyword, etc. (usually max five keywords per field)

I want to put all the individual keywords into one field. I could brute force it by finding Key1, Key2, Key3, etc.

from each original field, and then finding someway to concatenate the fields, but I wondered if there was

a more elegant way to do it.

thanks!

[#2]

Hi,

Try this

Key1 & Key2 & Key3 & Key4 & Key5 as MainKey,  // in script editor when you load your table

or just

Key1 & Key2 & Key3 & Key4 & Key5  // In a chart

Best regards.

[#3]

Hi,

Can you post an example?

[venkatg6759]

In script

If(Field ='keyword', 'Keyword',

   If(Field ='different keyword', 'different Keyword',

     If(Field ='new keyword', 'new Keyword')))

as OriginalField

[Anonymous]

Hello.

If you know you have max 5 keywords, they are separated by semicolons, and you want each one in its own field, you can use SubField():

Load

     SubField(MYFIELD, ';', 1) as KeyWord1,

     SubField(MYFIELD, ';', 2) as KeyWord2,

     SubField(MYFIELD, ';', 3) as KeyWord3,

     SubField(MYFIELD, ';', 4) as KeyWord4,

     SubField(MYFIELD, ';', 5) as KeyWord5

From etc...

On the other hand, if you want the keywords separated but in only one field (raising the number of records), it would be like this:

Load

     SubField(MYFIELD, ';') as KeyWord

From etc...

If you don't pass a third parameter to SubField(), it will put each value in a new record.

Hope it helps.

[swarup_malli]

Hey Kevin,

I had worked on a similar requirement long ago

Check this link out it should help.

http://community.qlik.com/message/327257#327257

Thanks

SM

[#7]

The field might contain:

"500W LAMP; Light fixture; Underwater lamp"

I want this in ONE field, Keywords, as three separate entries:

500W LAMP

Light fixture

Underwater lamp

The reason is I want to do some matches against another field.

thanks

[#8]

Hi Bruno,

Thanks, this looks like it will do what I want. I will test it out, and if it works, I'll come back to mark this Correct.

thanks again!

[maxgro]

t:

load * inline [

field

500W LAMP; Light fixture; Underwater lamp

500W MP; ht fixture; Undrwater lamp

50W LAMP; Ligfixture; Unrwater lap

50AMP; Light fire; Underr lamp

]

;

// all values in one field

f:

load subfield(field,';',1) as newfield resident t;

concatenate (f) load subfield(field,';',1) as newfield resident t;

concatenate (f) load subfield(field,';',2) as newfield resident t;

concatenate (f) load subfield(field,';',3) as newfield resident t;

concatenate (f) load subfield(field,';',4) as newfield resident t;

[MarcoWedel]

Just use the two parameter version of the subfield function.