Extracting a specific section from a long text field?

[#1]

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

[erichshiino · Accepted solution]

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

[#3]

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!

[#4]

Erich;

Works brilliantly. Thank you kindly.

Regards;

Kiz

[mohan2391]

Hi,

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

Thanks

[joaopassos97]

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?