topic Re: LOAD multiple items from one field into a single field in QlikView

LOAD multiple items from one field into a single field

Wed, 18 Jun 2014 17:28:08 GMT

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!

Re: LOAD multiple items from one field into a single field

Wed, 18 Jun 2014 17:34:23 GMT

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.

Re: LOAD multiple items from one field into a single field

Wed, 18 Jun 2014 17:35:23 GMT

Hi,

Can you post an example?

Re: LOAD multiple items from one field into a single field

venkatg6759 — Wed, 18 Jun 2014 17:37:51 GMT

In script

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

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

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

as OriginalField

Re: LOAD multiple items from one field into a single field

Anonymous — Wed, 18 Jun 2014 17:40:40 GMT

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.

Re: LOAD multiple items from one field into a single field

swarup_malli — Wed, 18 Jun 2014 18:01:27 GMT

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

Re: LOAD multiple items from one field into a single field

Wed, 18 Jun 2014 18:05:07 GMT

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

Re: LOAD multiple items from one field into a single field

Wed, 18 Jun 2014 18:08:01 GMT

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!

Re: LOAD multiple items from one field into a single field

maxgro — Wed, 18 Jun 2014 18:13:26 GMT

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

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;

Re: LOAD multiple items from one field into a single field

MarcoWedel — Wed, 18 Jun 2014 19:00:21 GMT

Just use the two parameter version of the subfield function.