topic Re: Extracting a specific section from a long text field? in QlikView

Extracting a specific section from a long text field?

Tue, 26 Jul 2011 00:16:02 GMT

G'day guys,

Having some trouble with this one:

Background: Reporting on some measurements that are to be pulled from a 'Product Description' field. The field is of no consistent length and the only identifier I have is the unit of measurement (um) within the field.

Examples:

'EXAMPLEPRODUCT 400UM'

'EG TESTING 1/S 50UM'

'ANOTHER TEST 1100UM'

This field also has some entries which are measured in GSM and other units but we are ONLY interested in the 'UM' measurements at this stage,

I don't know which function will do this for me with any accuracy due to the inconsistent varying of field length and number of digits in the actual measurement length. Hopefully someone has a lead I can follow on this one, it's the last piece of this nearly finished puzzle I have been working on.

Regards;

Kiz

Extracting a specific section from a long text field?

erichshiino — Tue, 26 Jul 2011 00:48:20 GMT

Hi,

If all your strings are like this you can use the following expression:

=LEFT(SUBFIELD(FIELD, ' ', SubStringCount(FIELD,' ')+1), LEN(SUBFIELD(FIELD, ' ', SubStringCount(FIELD,' ')+1))-2)

I count the number of spaces, then I use this number go get the last part of your string. Then, I remove the two last characters from this sub-string.

The steps are

1) no of spaces ( =SubStringCount(FIELD,' '))

2) get the text after the last space (=SUBFIELD(FIELD, ' ', SubStringCount(FIELD,' ')+1))

3) from the substring from (2), get all but the last two characters (=LEFT(SUBFIELD(FIELD, ' ', SubStringCount(FIELD,' ')+1), LEN(SUBFIELD(FIELD, ' ', SubStringCount(FIELD,' ')+1))-2))

Hope this helps,

Erich

Extracting a specific section from a long text field?

Tue, 26 Jul 2011 01:53:36 GMT

Erich;

This is certainly very helpful, I will run some tests with the data set and confirm your response as the correct answer if it works.

Thank you!

Extracting a specific section from a long text field?

Tue, 26 Jul 2011 03:14:34 GMT

Erich;

Works brilliantly. Thank you kindly.

Regards;

Kiz

Re: Extracting a specific section from a long text field?

mohan2391 — Wed, 16 Dec 2015 05:41:23 GMT

Hi,

Where to write this expression. I'm New to Qlikview.

Thanks

Re: Extracting a specific section from a long text field?

joaopassos97 — Tue, 13 Mar 2018 15:47:53 GMT

I'm having a problem that looks like yours:

I have extracted a file from my system's configuration and I want to organize the configuration parameters in tabs.

The main problem is: The file is organized from up to down, wich means that everything is going to be in a single tab "@1".

I need to search the whole file to get the parameters I want. Here's an example:

MODULE TAG="LIC-206A_1" PLANT_AREA="AREA_A/CALDEIRAS" CATEGORY=""

user="ADMINISTRATOR" time=1164283218/* "23-Nov-2006 09:00:18" */

{

DESCRIPTION="Master PID control loop"

PERIOD=5

CONTROLLER=""

PRIMARY_CONTROL_DISPLAY=""

INSTRUMENT_AREA_DISPLAY="LOOP_FP"

DETAIL_DISPLAY="LOOP_DT"

TYPE="Analog Control"

SUB_TYPE="CASCADE_MASTER"

ASSIGN_BLOCKS_TO_H1_CARD=F

FUNCTION_BLOCK NAME="AI1" DEFINITION="FFAI"

{

DESCRIPTION="Analog Input"

ID=100376530

RECTANGLE= { X=40 Y=180 H=56 W=140 }

}
}

This above is a sample of an amount of 4k+ "tags".

What I want to do is : The red ones turn into the title of the sheet and the green ones are going to be the items of the table.
like this:

Is the Qlik Sense able to do what I need it for?

If "yes", how can I do it?