Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Not applicable

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

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?

5 Replies
MVP
MVP

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

Is there a specific pattern it follows?

Not applicable

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

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

MVP
MVP

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

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?

Hi,

maybe one solution could be:

QlikCommunity_Thread_268925_Pic1.JPG

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

okolyug16
Contributor II

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

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

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

between.PNG