Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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.

1 Solution

Accepted Solutions
MarcoWedel

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

View solution in original post

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

Subfield3.png

Hope this is your solution.

Regards

Anand

MarcoWedel

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

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


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

MarcoWedel

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

View solution in original post

Anonymous
Not applicable
Author

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
Not applicable
Author

Thank you! I do appreciate that!

MarcoWedel

Glad I could help.

regards

Marco