topic Re: How to extract specific data from a string field in QlikSense? in App Development

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

Tue, 25 Jul 2017 17:30:58 GMT

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?

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

sunny_talwar — Tue, 25 Jul 2017 17:40:29 GMT

Is there a specific pattern it follows?

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

Tue, 25 Jul 2017 17:45:13 GMT

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

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

sunny_talwar — Tue, 25 Jul 2017 18:07:14 GMT

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.

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

MarcoWedel — Tue, 25 Jul 2017 21:58:49 GMT

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

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

Anonymous — Tue, 25 Jul 2017 22:15:20 GMT

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

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