How to extract specific data from a string field in QlikSense?

[#1]

Hi there, I need help, I have a field that contains the subject of an email and within that subject I need to extract a ticket number that contains both letters and numbers, i.e. AB123456, which function can I use to extract that number from the field?

[sunny_talwar]

Is there a specific pattern it follows?

[#3]

Yes, the ticket number is always first two letters are always text characters, followed by 6 numerical characters, as far as where the ticket number is located, there is no specific rule that dictates where the ticket could be in the subject of the emails, it's worth mentioning that the ticket number should be always in between square brackets

[sunny_talwar]

One other thing is that, is it possible (may be by chance that there are another string that follows the same pattern? If it is not possible then we might be able to figure this out.

Also, ask marcowedel‌ to take a look at this as he has been known expert in this area of finding patterns in the data.

[MarcoWedel]

Hi,

maybe one solution could be:

tabMails:
LOAD *,

    Mid(subject,Index(
    Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(
    Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(
    Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(
    Replace(Replace(Replace(Replace(Replace(Upper(' '&subject)
    ,'1','0'),'2','0'),'3','0'),'4','0'),'5','0'),'6','0'),'7','0'),'8','0'),'9','0')
    ,'B','A'),'C','A'),'D','A'),'E','A'),'F','A'),'G','A'),'H','A'),'I','A'),'J','A'),'K','A')
    ,'L','A'),'M','A'),'N','A'),'O','A'),'P','A'),'Q','A'),'R','A'),'S','A'),'T','A'),'U','A')
    ,'V','A'),'W','A'),'X','A'),'Y','A'),'Z','A')
    ,'[AA000000]'),8) as TicketNoSquareBrackets,

    Mid(subject,Index(
    Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(
    Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(
    Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(
    Replace(Replace(Replace(Replace(Replace(Replace(Upper(' '&subject&' ')
    ,'1','0'),'2','0'),'3','0'),'4','0'),'5','0'),'6','0'),'7','0'),'8','0'),'9','0')
    ,'B','A'),'C','A'),'D','A'),'E','A'),'F','A'),'G','A'),'H','A'),'I','A'),'J','A'),'K','A')
    ,'L','A'),'M','A'),'N','A'),'O','A'),'P','A'),'Q','A'),'R','A'),'S','A'),'T','A'),'U','A')
    ,'V','A'),'W','A'),'X','A'),'Y','A'),'Z','A'),'[',' '),']',' ')
    ,' AA000000 '),8) as TicketNo

Inline "
subject
some header including ticket number [AB123456]
no ticket number included
some other A12345 text
ticket number BC234567 without square brackets
another [CD345678] ticket
abcdefg [X1234567] hijklmnop
abcdefg [12345678] hijklmnop
abcdefg [XYZ12345] hijklmnop
abcdefg [XY123456] hijklmnop
abcdefg [XY1234567] hijklmnop
bcdefg [XY123456]
[XY12345A] efghijkl
12345 [YZ12345]
1234567 [Y123456] 890
EF098765
FG09A765
[FG0987M5]
[FG098765]
";

see also:

Please help me get the string pattern of each field.

Finding text patterns in Data Columns

hope this helps

regards

Marco

[Anonymous]

A combination of the TextBetween and Right functions might do the trick;

     Right((TextBetween(subject,'[',']',1)),6)