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.



      "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.











      "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



      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.