Choose Language Hide Translation Bar
Community Trekker

Create new data tables based on ID# in column 1, script help!

Hi again! My question today is in regards to the creation of data tables all from information found in a single jmo table. In column 1 I have a bunch of different ID numbers. This table I have right now is like 60,000 rows long so I need to know how to script something to create multiple data tables. Here is an example breakdown of my table... Column1 is ID number, column 2 is size, column 3 is temperature, column 4 is zip code, column 5 is cost. I have more columns but for the purpose of keeping this post short and readable, we'll move on. Now I want to open up new tables based on the ID#. The trick here is that for each ID number, there is a different number of rows, like ID# CAL-105-large might have 60 rows of data, then CAL-110-large might only have 20. Another thing is that the ID#s do not go in order, like the first set isnt example_id_1 followed by example_id_2, and example_id_3 etc. So, to sum up the question: I want to open up a bunch of new data tables, each containing rows of data that all have the same ID number. The constarints are that the ID numbers do not follow in any specific order, but are currently grouped together. Another constraint is that not all ID numbers have the same number of rows. I have about 60,000 rows of data, and and about ~2000 unique ID numbers. If someone could help me with a script to open up a bunch of new data tables with only data from unique id number that would be great! Thanks!

0 Kudos
Super User ms
Super User

Re: Create new data tables based on ID# in column 1, script help!

The quick way is:

  dt << Subset(  By( :ID number ))

However, it may be annoying with 2000 open tables. If you want to do something with each table a loop is better. Something like this should work:

dt = Open( "$SAMPLE_DATA/Big" );

Summarize( g = by( Column( dt, "sex" ) ) );

n = N Items( g );

For( i = 1, i <= n, i++,

  dt << select where( Char( :sex[] ) == g[i] ); // Char() is only needed if ID is numeric

  dtsub = dt << Subset( selected rows );

  //do something with dtsub here, e.g. analyses, save and close before moving on...


0 Kudos