I have an ugly database with a text narrative. Within the narrative, there is a reference to a patient number. The patient number is always in the format:
A is any letter A-Z (either capital or lowercase) or any digit
0 is any numeric character.
Total characters is 6.
A123456, 112112, b11234
I think I need to use the findoneof() function, and finding 5 consecutive numeric characters, and then using that position within the string and the Mid() function to select the 6 character patient number. The coding I'm imagining is so inelegant that I hesitate to begin. How do I find the first numeric character without searching separately for 1, then 2, then 3, etc.
any help is appreciated.
If you want only the number and eliminate the text, you can use the function KeepChar:
or use PurgeChar
Hope This Helps,
Thanks, Jose. The problem is there may be additional numbers referenced within the original text. I think I'll have to use a for/next loop to go character-by-character and look for consecutive numeric values.
I think there are two alternatives to your loop over all chars from the fieldvalues. One could be a RegEx approach like described here: http://www.qlikfix.com/2010/10/18/regular-expressions-in-the-load-script/ and the other to use a mapsubstring() maybe similar to this one:
load F1 & iterno() while iterno() <= 999999;
load chr(64 + recno()) as F1 autogenerate 26;
This won't be a small table but it wouldn't be too huge to be usable. Whereby I'm not sure if you really need to create this as a large generic table - your patient numbers should be already available in any fact- or dimension-table. In this case your mapping-table would be look like:
mapping load distinct [Patient Number], '<' & [Patient Number] & '>' from YourTable;
load *, textbetween(mapsubstring('Map', FreeText), '<', '>') as [Patient Number] from TableWithFreeText;
Depending on the text you might need a different delimiter for textbetween() which definitely exists within the text, for example a chr(1) should be quite unlikely.
May be something like this..
Load * Inline
IF(IsNUm(Mid(TTT,IterNo()+1,1)) AND IsNUm(Mid(TTT,IterNo()+2,1)) And IsNUm(Mid(TTT,IterNo()+3,1))And IsNUm(Mid(TTT,IterNo()+4,1)) And IsNUm(Mid(TTT,IterNo()+5,1)) and Istext(Mid(TTT,IterNo(),1)),IterNo(),0) as IndexOfPatNo
Mid(TTT,IndexOfPatNo,7) as PatientNumber,
thanks to everyone for the suggestions. After soliciting the help of the OIT department here, they decided to create the new field in a step prior to my loading script.