3 Replies Latest reply: Dec 22, 2017 8:00 AM by Twan Peters RSS

    Extract name out of string

    Twan Peters

      A follow-up from https://community.qlik.com/message/1377864#1377864

       

      I'm trying to extract a ticket ID out of a string.

       

      Example strings:

      CL AR 20171211 PHSCRC CA ADHOC Conversion Time Off - Diciembre 2017 #NMQ-644-92867

      TicketID =#NMQ-644-92867

       

      CL AR 20171211 PHSCRC CA ADHOC Conversion Time Off - Diciembre 2017 NMQ-644-92867

      TicketID = NMQ-644-92867


      CL RU 20161017 CRBCRC CA CLCP Welcome LGJ-309-18486

      TicketID = LGJ-309-18486

       

      CL BG 20161121 CRBCRC CA CLCP Cross sell Black Friday Campaign

      TicketID = (BLANK)

       

      If there is a ticketID it always placed at the end of the string and always consist out of:

      xxx-xxx-xxxxx

      or

      #xxx-xxx-xxxxx


      Currently it almost works with the following function:

      Trim(Mid(Name, Index(Name, '-')-4)) as Ticket,


      It almost works, if the '-' is also used before the ticketID it basically fails:

      CL AR 20171211 PHSCRC CA ADHOC Conversion Time Off - Diciembre 2017 #NMQ-644-92867

      Result TicketID = Off - Diciembre 2017 #NMQ-644-92867

      While it should be: #NMQ-644-92867


      Anyone who has an idea for this?