3 Replies Latest reply: Nov 19, 2017 3:32 PM by Quy Nguyen RSS

    How to extract numbers out of varying text fields

    Tyler Emberson

      Hello Qlik Community,

       

      I am struggling to find a way to pull out numbers from text fields. I will start off with two examples of our text fields.

       

      First:

      "BME sample of ___ collected @1000, had a micro result out of spec. Placing affected time frame between good checks @0800-1200 on Cat 1 hold for further review.

       

      LPNs

      31571058

      31571094

      31571098

      31571105

      31571111

      31571143"

       

      Second:

      "R9B1 had a safety shutdown during the come-up fill due to an open door during process, which lead to an uncleared deviation. It was then restarted as R9B2. Unsure of the root cause, operators believe it to be a false reading, possibly due to a sensor issue. CAT1 Hold for further review.

       

      LPNs and quantity will be updated.

       

      UPDATE - TM

       

      LPNs

      31571149                      190513          QC      TR0900        

      31571242                      190513          QC      TR0900        

      31571244                      190513          QC      AB06FLOOR"

       

       

      The issue is I need to separate out the LPNs from the rest of the text and put them into their own field per 8 digit LPN. We are debating creating a new table and querying to populate the table creating a record for each LPN. Then importing that table. All LPNs start with the number 3 but it can vary after that.