Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
Highlighted
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