tool mastery

[razzy]

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

[tcroberts]

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!

[razzy]

I know how to separate the column what i want to do is to maybe keep the two variable and then join the numeric part to another table column and then output the values as the text ( for example join 1 and 1 output registration)

Ibrahim George-Sankoh

Data Integration Analyst

(215)-590-1933

[tcroberts]

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.

[razzy]

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.

[tcroberts]

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.

[razzy]

I have exact matches those values are just examples.


Ibrahim George-Sankoh

Data Integration Analyst

(215)-590-1933

[tcroberts]

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