split text from field

[amiroh81]

Hi All,

I have a field that contains random text and I want to split the text into single words.

The text can be separated by spaces, commas, periods, etc.

I tried using SubField with a space separator but I only split the first word.

In addition, I was unable to separate more than one type of separator.

For example, I have the following sentence:

how are-you.doing today

The result I would like to have is:

how

are

you

doing

today

Thank you!

[YoussefBelloum]

Hi,

test:

LOAD *,

subfield(Replace(Replace(name,'-', ' '),'.',' '),' ',1) as String1,

subfield(Replace(Replace(name,'-', ' '),'.',' '),' ',2) as String2,

subfield(Replace(Replace(name,'-', ' '),'.',' '),' ',3) as String3,

subfield(Replace(Replace(name,'-', ' '),'.',' '),' ',4) as String4

Inline [

name

how are-you.doing today

];

[vishsaggi]

May be try this using Mapping load. You can add any special characters in the mapping table named Table1 as you go.

Table1:

Mapping LOAD * INLINE [

Text, String

' ', @

-, @

., @

',', @

];

LOAD *, Subfield(replaceString, '@') AS NewField;

LOAD *, MapSubString('Table1', Text) AS replaceString INLINE [

Text

how are-you.doing today

];

[YoussefBelloum]

Great !

[antoniotiman]

Try this

LOAD SubField(SubField(SubField(Field,' '),'.'),'-') as Field

[rwunderlich · Accepted solution]

CharMap:

Mapping LOAD *, ' '

Inline [

char

.

,

-

] (delimiter is '\t');

data:

LOAD

SubField(MapSubString('CharMap', Input), ' ') as Word

Inline [

Input

how are-you.doing today

];

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

[YoussefBelloum]

much greater

[vkish16161]

Viswarath's solution is the best of the lot. It will work.

[amiroh81]

Thank you all.

Is there a way to make the separation in the model itself and not in the script?

where  can i read about mapping load?

[rwunderlich]

http://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/ScriptPrefixes/...