Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

rrodriguesrr
New Contributor III

Character Functions: Take a piece of a text

Hi folks,

I wonder if you can help me with this chalenge!

I have the following cell from a excel sheet:

Unit

Unit A - CLA_99999 - Financial

Uinit B1 - CLA_88888 - Controlership

Unit B2 - CLA_77777 - Human Resources

What I need is to take the piece of information that start with 'CLA_*', but as this is a unique cell with 3 line breaks, I don't know how to take the three 'CLA_ (CLA_99999, CLA_88888 and CLA_77777) from this full string and discard the rest.

I appreciate any help in advance.

Best,


Rubens Rodrigues.

Tags (2)
1 Solution

Accepted Solutions

Re: Re: Character Functions: Take a piece of a text

Hi,

one solution to retrieve all 'CLA_*' values:

QlikCommunity_Thread_145050_Pic1.JPG.jpg

LOAD *

Where Unit like 'CLA_*';

LOAD Textbetween(Unit, ' - ', ' - ',IterNo()) as Unit

FROM [http://community.qlik.com/thread/145050] (html, codepage is 1252, embedded labels, table is @1)

While IterNo()<=SubStringCount(Unit,' - ');

hope this helps

regards

Marco

7 Replies

Re: Character Functions: Take a piece of a text

Hi,

Write like this way by use subfield

LOAD Unit, SubField(Unit,'-',2) as NewField;

LOAD * Inline  [

Unit

Unit A - CLA_99999 - Financial

Uinit B1 - CLA_88888 - Controlership

Unit B2 - CLA_77777 - Human Resources   ];

Subfield3.png

Hope this is your solution.

Regards

Anand

Re: Character Functions: Take a piece of a text

Textbetween(Unit, 'CLA_', ' -', 1)

Textbetween(Unit, 'CLA_', ' -', 2)


Textbetween(Unit, 'CLA_', ' -', 3)

Re: Re: Character Functions: Take a piece of a text

Hi,

one solution to retrieve all 'CLA_*' values:

QlikCommunity_Thread_145050_Pic1.JPG.jpg

LOAD *

Where Unit like 'CLA_*';

LOAD Textbetween(Unit, ' - ', ' - ',IterNo()) as Unit

FROM [http://community.qlik.com/thread/145050] (html, codepage is 1252, embedded labels, table is @1)

While IterNo()<=SubStringCount(Unit,' - ');

hope this helps

regards

Marco

rrodriguesrr
New Contributor III

Re: Character Functions: Take a piece of a text

Hey Marco - It really worked!!!

I do appreciate your help!

Actually I did not understand the usage of ITERNO() in this script.

Would you mind to explain it to me?

Thank you!


Rubens

Re: Re: Character Functions: Take a piece of a text

Hi,

you're welcome.

the while/IterNo() generates multiple output records from one input record.

In this case it counts from 1 to the number of delimiter strings ' - ' in your input string.

For each IterNo() the Textbetween() function extracts one 'CLA_*' substring.

hope this helps

regards

Marco

rrodriguesrr
New Contributor III

Re: Character Functions: Take a piece of a text

Thank you! I do appreciate that!

Re: Re: Character Functions: Take a piece of a text

Glad I could help.

regards

Marco

Community Browser