Hi everyone, I would like to ask about this data set IDColumn AColumn BColumn C Column D1A*05:01 A*25:01 B*08:01 B*39:01 C*05:01 C*07:06 C*07:18 C*12:03 DRB1*05:01 DRB1*07:01 DRB3*01:01 DRB4*09:03N DQA1*02:01 DQA1*05:01 DQB1*02:01 DQB1*02:02DQA1*01-03,DQB1*06-03_DQ6 2A*20:01 A*60:02 B*08:01 B*28:03 C*02:02 C*03:04 DRB1*01:02 DRB1*13:04 DRB3*02:02 DRB3*02:02 DQA1*01:01 DQA1*01:04 DQA1*01:05 DQA1*05:05 DQA1*05:09 DQB1*02:01 DQB1*05:01DQA1*05-01,DQB1*02-01_DQ2 3A*01:01 A*01:01 B*08:01 B*38:01 C*06:02 C*09:01 C*07:06 C*07:18 DRB1*07:01 DRB1*07:01 DRB4*01:01 DRB4*01:03 DRB4*01:05N DQA1*02:01 DQA1*02:01 DQB1*02:02 DQB1*03:03DQA1*03-03,DQB1*04-01_DQ4 4....A*01:01 A*01:01 B*08:01 B*38:01 C*06:02 C*09:01 C*07:06 C*07:18 DRB1*07:01 DRB1*07:01 DRB4*01:01 DRB4*01:03 DRB4*01:05N DQA1*02:01 DQA1*03:03 DQB1*02:02 DQB1*03:03DQA1*03-03,DQB1*04-01_DQ4DQA1*02:01 DQA1*03:03 DQB1*02:02 DQB1*03:03DQA1*03:03 Q1: How can i do? I want to extract text only DQA1... and DQB1... in column A into column C. Q2: How can I do? I want to match the extract text in column B and column C in the same row into column D I try to use the regex command - but the problem occur with * and :, and I have no idea how to match part of text with two column in multiple ID... I put the thing that I want into the ID 4., in this case : and - are the same. Thank you for your help in advance!

nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

How to match part of text in 2 column?

doraemengs

Hi everyone, I would like to ask about this data set

ID	Column A	Column B	Column C	Column D
1	A05:01 A25:01 B08:01 B39:01 C05:01 C07:06 C07:18 C12:03 DRB105:01 DRB107:01 DRB301:01 DRB409:03N DQA102:01 DQA105:01 DQB102:01 DQB102:02	DQA101-03,DQB106-03_DQ6
2	A20:01 A60:02 B08:01 B28:03 C02:02 C03:04 DRB101:02 DRB113:04 DRB302:02 DRB302:02 DQA101:01 DQA101:04 DQA101:05 DQA105:05 DQA105:09 DQB102:01 DQB1*05:01	DQA105-01,DQB102-01_DQ2
3	A01:01 A01:01 B08:01 B38:01 C06:02 C09:01 C07:06 C07:18 DRB107:01 DRB107:01 DRB401:01 DRB401:03 DRB401:05N DQA102:01 DQA102:01 DQB102:02 DQB1*03:03	DQA103-03,DQB104-01_DQ4
4....	A01:01 A01:01 B08:01 B38:01 C06:02 C09:01 C07:06 C07:18 DRB107:01 DRB107:01 DRB401:01 DRB401:03 DRB401:05N DQA102:01 DQA103:03 DQB102:02 DQB1*03:03	DQA103-03,DQB104-01_DQ4	DQA102:01 DQA103:03 DQB102:02 DQB103:03	DQA1*03:03

Q1: How can i do? I want to extract text only DQA1... and DQB1... in column A into column C.

Q2: How can I do? I want to match the extract text in column B and column C in the same row into column D

I try to use the regex command - but the problem occur with * and :, and I have no idea how to match part of text with two column in multiple ID...

I put the thing that I want into the ID 4., in this case : and - are the same.

Thank you for your help in advance!

Accepted answers

jthi

For Column D you could use Words(), Substitute() and Associative Array() + Intersect, in a formula, to find exact matches

aa1 = Associative Array(Words(:Column B, ",")); 
aa2 = Associative Array(Words(Substitute(:Column C, ":", "-"), " \!N"));
aa1 << Intersect(aa2);
Concat Items(aa1 << Get Keys, ",");

For Column C you can get good ideas from Regex: can't get all parts of the string that match

Edit:

Here is one option for Column C which might work. If you don't understand what it does, I suggest using While loop and Regex as that is generally easier to understand than JMP's pattern matching.

result = {};
Pat Match(
	:Column A,
	Pat Repeat(
		(Pat Regex("(DQ[AB]1.+)[\s$]") >> result)
	| 
		Pat Len(1)
	)
);
Concat Items(Words(result, " "), ", ");

jthi

Open the table and check out the formulas added to Column C and Column D (they both have formulas).

If you are not sure how to create formulas in JMP, check out JMP Help page https://www.jmp.com/support/help/en/17.2/#page/jmp/create-formulas-in-jmp.shtml#

All comments

jthi

For Column D you could use Words(), Substitute() and Associative Array() + Intersect, in a formula, to find exact matches

aa1 = Associative Array(Words(:Column B, ",")); 
aa2 = Associative Array(Words(Substitute(:Column C, ":", "-"), " \!N"));
aa1 << Intersect(aa2);
Concat Items(aa1 << Get Keys, ",");

For Column C you can get good ideas from Regex: can't get all parts of the string that match

Edit:

Here is one option for Column C which might work. If you don't understand what it does, I suggest using While loop and Regex as that is generally easier to understand than JMP's pattern matching.

result = {};
Pat Match(
	:Column A,
	Pat Repeat(
		(Pat Regex("(DQ[AB]1.+)[\s$]") >> result)
	| 
		Pat Len(1)
	)
);
Concat Items(Words(result, " "), ", ");

doraemengs

Thank you for your advice, but on column C generation, the error occurred as

attempting to assign to an object that is not an L-value in access or evaluation of 'Assign' , Current Data Table() << result = /*###*/{} /*###*/" and

Must be a string or 1x2 matrix in access or evaluation of 'Words' , Bad Argument( result ), Words/*###*/(result, " ")

please guided me more!

Thank you

jthi

Did you copy paste the formula there directly? As I don't have your data table there might differences in the separators used (see the attached file).

735102.jmp

doraemengs

Hi Jarmo, Thank you for your help again. But, even I use your 735102.jmp dataset and copy/paste the command, it don't work. Could you please to provide me the script on 735102.jmp file?

Thank you!