363431_en-US

liliconcom

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

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?

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

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?

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

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

 

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

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.

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

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=""></NEW>

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

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.