Character Functions: Take a piece of a text

[Anonymous]

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.

[its_anandrjs]

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   ];

Hope this is your solution.

Regards

Anand

[MarcoWedel]

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

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

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

[MarcoWedel · Accepted solution]

Hi,

one solution to retrieve all 'CLA_*' values:

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

[Anonymous]

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

[MarcoWedel]

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

[Anonymous]

Thank you! I do appreciate that!

[MarcoWedel]

Glad I could help.

regards

Marco