This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
Hi all ,
I have a field that contains numeric value and letters. i can seperate them if the need be, but what i am trying to do is to link the join the numeric part with another table and output the letter parts, i am not sure what tool i can use to do this. As an example
Field_1
1 - Registration
2 - Walk-In
3 - Hospital Encounter
5 - Canceled
6 - Unmerge
7 - Contact Moved
10 - EMPTY
11 - Research Encounter
I want to link 11 form another table and get research encounter as the join output.
Can someone help me figure this out?
Thanks
You probably want some sort of Regex.
Likely using the RegEx Tool, configured to Parse so you can extract multiple columns out at once.
If you use:
'(\d+)\s-\s(.*)'
You'll create two columns: one with the digits, and one with the text.
Depending on the data type in the table you want to match to, you may need to then change the numeric part to INT16 or something, using a Select Tool.
Let me know if this helps, or if the RegEx isn't working as expected,
Cheers!
Yes, if you split them like mentioned above, then you can join on the Numeric part, and you'll be adding the "Registration", or text part to the table you're joining on.
If you can provide some sample data i'd be glad to put together a sample workflow.
thanks for this , so lets say we have column1 from table 1 like this:
ENC_TYPE_C
109
109
109
109
115
109
115
109
50
50
109
109
109
101
50
50
101
table 2 has column 1 also as below
variables
1 - Registration
2 - Walk-In
3 - Hospital Encounter
5 - Canceled
6 - Unmerge
so i want to join the numeric part of table 2 column 1 to that of the column1 table 1 and get an output of the the second part of table 2. for instance 1 and 1 join give registration as shown that 1 in table2 column 1 means registration.
So are you trying to look for "101" to match to 1, or will there be an exact match in the ENC_TYPE_C column?
As is, you could probably check if the ENC_TYPE_C = 50, otherwise subtract 100 and then use Join Tool to join on that, removing the unnecessary columns when you're done. However, to figure something out to do this in general I'd need a better understanding of all the possible ENC_TYPE_C values.
Then yes, a Join Tool matching on the numeric parts should get you what you want. Just remember to remove the unwanted columns, and rename things as desired in the Configuration Pane
You would join ENC_TYPE_C on RegExOut1 (the numeric part). Make sure to first use a Select tool to convert them both to the same data type and remove any leading/trailing whitespace