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; } } }

Limit on string length used in Table Summary and in Select Duplicate Rows

JerryFish

This is an FYI, and perhaps a question too.

A client asked today if there was a limit on the string length used to judge whether rows are duplicates, or when doing a Table Summary. She had strings that were 8000 characters long in a single cell. (This was genetic marker data.) She wanted to see how many true duplicates she had in a column of data. Neither were reporting the correct rows. She suspected a string length problem.

I did some brute force comparisons, and found that only the first 2000 characters are compared when doing Table Summary or Select Duplicate Rows.

I also found that doing something like "if(col1==col2,"Match","No Match") does not have the 2000 character limit. (In other words, the entire string (at least up to 8000 characters) appears to be used in the comparison.)

I don't know about other functions.

So this is an FYI to beware comparison of long strings... and a question: Does anyone know why we limit to 2000 characters in these cases?

Accepted answers

chungwei

Looks like it is a problem, in several places, summary, data filter, distribution.

Maybe in more places, I have not checked all the places that categorize column data.

It is a bug.

chungwei

I think the limit was put there for performance. I saw the comment

// very long lengths make performance unbearable

It is probably reasonable for platforms. It is very unlikely somebody want to see strings that long displayed as a category.

However for finding duplicates, we probably should not cede correctness to performance.

This will be fixed for 16.1.

All comments

Chris_Kirchberg

Hi Jerry,

I am speculating here, but I think what your customer is doing with Table Summary or Select Duplicate Rows is inappropriate. Can you give us more details? Is it genetic code that is 8000 characters long and consists of only A, C, G, and Ts? If so, this would be an inappropriate approach. It sounds like they are trying to fit a square peg in a round hole,.

If they really want to use JMP for this, it would be better to make a single column of every character and then look for duplicates between rows.

More important is their goal. Why are they looking for duplicates?

JerryFish

Good point Chris. Yes, they are looking at ACGT strings. My impression is that she had 2000 groups of 4 characters, all strung together into an 8000 character string. She didn't give further details aside from wanting to compare one 8000 character string to another and look for duplicates.

Since I don't know about ACGT strings and how they are used, I didn't know to ask about this. (However, I did know enough to recognize that she might be interested in JMP Genomics. They previously weren't aware of JMPG, but they are now nibbling!)

I'll ask about what she is trying to do. Appreciate the feedback!

Jerry

Jeff_Perkinson

Interesting. It looks like the customer is right.

dt1=New Table( "2000 characters",
	Add Rows( 2 ),
	New Column( "Column 1",
		Character,
		"Nominal",
		Formula(
			If( Row() == 1,
				Repeat( "A", 1999 ) || "1",
				Repeat( "A", 1999 ) || "2"
			)
		),
	)
);

//this summary table will have two rows
dt1<< Summary(
	Group( :Column 1 ),
	Freq( "None" ),
	Weight( "None" )
);

dt2=New Table( "2001 characters",
	Add Rows( 2 ),
	New Column( "Column 1",
		Character,
		"Nominal",
		Formula(
			If( Row() == 1,
				Repeat( "A", 2000 ) || "1",
				Repeat( "A", 2000 ) || "2"
			)
		),
	)
);

//this summary table will have only one row
dt2<< Summary(
	Group( :Column 1 ),
	Freq( "None" ),
	Weight( "None" )
);

I'll bet @chungwei will have some insight.

Chris_Kirchberg

Sure thing Jerry!

There are other simple tools (Like the JSL code you used) that can look into long stings for comparison. Of special interest is that not only are the same characters in the string for the same length, but the order they are in. True duplicates in character and order appearance of those characters.

However, if every character (base/marker in this case) was in its own column and then all of those columns were selected to be used for select duplicate rows or table summary should work (but it may be slow).

Usually this is a bioinformatics problem and people might write code to solve it.

brady_brady

For what it is worth, the column formulas seem to compare strings at least as big as 8K characters.

The script below identifies which 27 of 30 rows are duplicates of 3, 8001-character strings differing only on the last character.

Names Default To Here(1);

a = repeat("0", 8000);

x = a ||"X";
y = a || "Y";
z = a || "Z";

vals = {x,y,z};

lis = {};

for(i=1, i<=30, i++,
	insertinto(lis, eval(vals[randominteger(3)]));
);
dt = new table("xx", <<new column("txt", character, <<set values(lis)));
dt << new column("num", formula(colcumulativesum(1, :txt)));
dt << select where (:num != 1);

JerryFish

Thanks all. @brady_brady and @Jeff_Perkinson , your analysis is more elegant than what I did, but we came to the same conclusion... 2000 char limit on Table Summary and Select Duplicate Rows, but formulas seem to recognize all characters.

Hoping @chungwei will have some additional insights, perhaps more functions that have this limitation?

chungwei

Looks like it is a problem, in several places, summary, data filter, distribution.

Maybe in more places, I have not checked all the places that categorize column data.

It is a bug.

chungwei

I think the limit was put there for performance. I saw the comment

// very long lengths make performance unbearable

It is probably reasonable for platforms. It is very unlikely somebody want to see strings that long displayed as a category.

However for finding duplicates, we probably should not cede correctness to performance.

This will be fixed for 16.1.

JerryFish

Thank you @chungwei ! I agree, we should be correct first. Maybe insert a check if string length>2000 characters, show a warning e.g. "very long string lengths can take some time to compute... are you sure?"

I really appreciate your responsiveness! Thank you.

chungwei

Actually, if each character has its own column, maybe a list check column, then finding duplicates from all those columns will probably be a lot faster. Dealing with very long strings is very expensive.