How to isolate text in a string and then use this to filter

[wriggall]

Greetings. I am new to QlikSense.

I am working with a data file (Excel) which is updated every 4 hours. The file contains between 300K to 500K rows.

One of the fields contains data in the following format FG12345-01-M1234-C6-10-ABC-DEF-000-01

Some rows might have the format like this FG12345-01-M1234-C6-10-ABC-DEFG-000-01 (text equals 4 characters and following  character set has 4 numbers instead of 3)

Or like this FG12345-01-M1234-C6-10-ABC-DEF-000-01-XYZ001 (an additional 6 characters including the hyphen)

I am wanting extract two character sets from the string FG12345-01-M1234-C6-10-ABC-DEF-000-01 and use these as filters on the full data set. These character sets are consistently the same number of characters in from the first character on the left.

The first 7 characters of the string are also in a field of their own.

How do I isolate and extract these 2 character sets and how would I use them as filters on the wider data set?

[jonathandienst · Accepted solution]

For this input/output:

FG12345-01-M1234-C6-10-ABC-DEF-000-01                        --> FG12345, ABC-DEF, -
FG12345-01-M1234-C6-10-ABC-DEFG-000-01                     --> FG12345, ABC-DEFG, -
FG12345-01-M1234-C6-10-ABC-DEF-000-01-XYZ001       --> FG12345, ABC-DEF, XYZ001

Let's call the input field "FullCode":

...
FullCode,

SubField(FullCode, '-', 1) as CodePrefix,
If(SubStringCount(FullCode, '-') = 8,
	SubField(FullCode, '-', -4) & '-' SubField(FullCode, '-', -3),
	SubField(FullCode, '-', -5) & '-' SubField(FullCode, '-', -4)
) as CodeFilter1,

If(SubStringCount(FullCode, '-') = 9,
	SubField(FullCode, '-', -1)
) as CodeFilter2,
...

Adjust the above code to the correct field name(s) and to match your exact requirement if I have not understood correctly.

[wriggall]

 Thank you for this solution. I have now got it working well in my App.