5 Replies Latest reply: Jul 25, 2017 6:15 PM by Ugonna Okoli RSS

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

    JORGE REYES

      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

          Is there a specific pattern it follows?

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

            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