topic Re: split text from field in QlikView

split text from field

amiroh81 — Wed, 15 Nov 2017 15:18:05 GMT

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!

Re: split text from field

YoussefBelloum — Wed, 15 Nov 2017 15:28:30 GMT

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

];

Re: split text from field

vishsaggi — Wed, 15 Nov 2017 15:41:54 GMT

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

];

Re: split text from field

YoussefBelloum — Wed, 15 Nov 2017 15:49:29 GMT

Great !

Re: split text from field

antoniotiman — Wed, 15 Nov 2017 15:49:37 GMT

Try this

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

Re: split text from field

rwunderlich — Wed, 15 Nov 2017 15:53:45 GMT

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

Re: split text from field

YoussefBelloum — Wed, 15 Nov 2017 16:04:21 GMT

much greater

Re: split text from field

vkish16161 — Wed, 15 Nov 2017 16:32:37 GMT

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

Re: split text from field

amiroh81 — Wed, 15 Nov 2017 18:11:43 GMT

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?

Re: split text from field

rwunderlich — Wed, 15 Nov 2017 18:30:24 GMT

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

Re: split text from field

amiroh81 — Wed, 15 Nov 2017 18:37:39 GMT

what about split a field on the model itself?

Re: split text from field

rwunderlich — Wed, 15 Nov 2017 18:48:21 GMT

What do you want to do with the split words? Show them in a textbox? or a listbox? Is it only one selection you want to process or all of them?

-Rob

Re: split text from field

amiroh81 — Wed, 15 Nov 2017 19:00:19 GMT

In the end I want to count the amount of words that appear in the sentence and how many times each word appeared.

It is important for me to do this in the model, because the counting should be done after the user performs several filters in the model

Re: split text from field

rwunderlich — Wed, 15 Nov 2017 20:48:40 GMT

You can break out the words in the script and leave them linked to the sentences. Then the words will reflect selections.

CharMap:

Mapping LOAD *, ' '

Inline [

char

] (delimiter is '\t');

Sentences:

LOAD *, RecNo() as RecId

Inline [

Sentence

how are-you.doing today

I am fine

];

Words:

LOAD

RecId,

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

Resident Sentences;

-Rob

Re: split text from field

MarcoWedel — Thu, 16 Nov 2017 22:34:47 GMT

Hi,

another method to define non-letter characters for the word separation might be (included in the already proposed SubField/MapSubString solution😞

mapNonLetterToSpace:
Mapping
LOAD Chr(RecNo()), ' '
AutoGenerate 65535
Where Upper(Chr(RecNo()))=Lower(Chr(RecNo()));

tabTextLines:
LOAD RecNo() as ID, *
Inline '
    TextLine
    "SubField - script and chart function"
    "Subfield() is used to extract substring components from a parent string field, where the original record fields consist of two or more parts separated by a delimiter."
    "The Subfield() function can be used, for example, to extract first name and surname from a list of records consisting of full names, the component parts of a path name, or for extracting data from comma-separated tables."
    "If you use the Subfield() function in a LOAD statement with the optional field_no parameter left out, one full record will be generated for each substring. If several fields are loaded using Subfield() the Cartesian products of all combinations are created."
    "Syntax:"
    "SubField(text, delimiter[, field_no ])"
    "Return data type: string"
    "Arguments:"
    "Argument Description"
    "text The original string. This can be a hard-coded text, a variable, a dollar-sign expansion, or another expression."
    "delimiter A character within the input text that divides the string into component parts."
    "field_no The optional third argument is an integer that specifies which of the substrings of the parent string text is to be returned. A negative value causes the substring to be extracted from the right-hand side of the string. That is, the string search is from right to left, instead of left to right, if field_no is a positive value."
';

tabWords:
LOAD Distinct * Where Len(Word);
LOAD ID,
    SubField(MapSubString('mapNonLetterToSpace',TextLine),' ') as Word
Resident tabTextLines;

hope this helps

regards

Marco

Re: split text from field

MarcoWedel — Fri, 17 Nov 2017 23:44:20 GMT

one example of calculating word frequencies and counting words per text line in the front end might be:

mapNonLetterToSpace:
Mapping
LOAD Chr(RecNo()), ' '
AutoGenerate 65535
Where Upper(Chr(RecNo()))=Lower(Chr(RecNo()));

tabTextLines:
LOAD RecNo() as LineID, *
Inline '
    TextLine
    "SubField - script and chart function"
    "Subfield() is used to extract substring components from a parent string field, where the original record fields consist of two or more parts separated by a delimiter."
    "The Subfield() function can be used, for example, to extract first name and surname from a list of records consisting of full names, the component parts of a path name, or for extracting data from comma-separated tables."
    "If you use the Subfield() function in a LOAD statement with the optional field_no parameter left out, one full record will be generated for each substring. If several fields are loaded using Subfield() the Cartesian products of all combinations are created."
    "Syntax:"
    "SubField(text, delimiter[, field_no ])"
    "Return data type: string"
    "Arguments:"
    "Argument Description"
    "text The original string. This can be a hard-coded text, a variable, a dollar-sign expansion, or another expression."
    "delimiter A character within the input text that divides the string into component parts."
    "field_no The optional third argument is an integer that specifies which of the substrings of the parent string text is to be returned. A negative value causes the substring to be extracted from the right-hand side of the string. That is, the string search is from right to left, instead of left to right, if field_no is a positive value."
';

tabWords:
LOAD
    LineID,
    Word,
    AutoNumber(IterNo,'WordNo'&LineID) as WordNo,
    AutoNumber(Hash128(LineID,IterNo),'WordID') as WordID,
    Upper(Word) as WORD
Where Len(Word);
LOAD LineID,
    IterNo() as IterNo,
    SubField(MapLine,' ',IterNo()) as Word
While IterNo()<=SubStringCount(MapLine,' ')+1;  
LOAD LineID,
    MapSubString('mapNonLetterToSpace',TextLine) as MapLine
Resident tabTextLines;

hope this helps

regards

Marco

Re: split text from field

amiroh81 — Thu, 30 Nov 2017 12:42:26 GMT

Thank you very much