227906_it-IT

liliconcom

Table rearrangement challenge

Hi-

I am trying to rearrange a table and am struggling to get the desired output. I have tried using the Summary Table, Join or Update menu functions in various permutations but have failed... any help would be extreamly appreciated- the actual table has 80,000 rows, so really can't do this manually...

 

I know there are no more than 10 thaw dates/thaw IDs for any given Sample ID in the real data set.

 

The Summary Table function quickly gets me to a table with each Sample ID listed only once (desired)- but I cannot figure out how to get the additional Thaw Date and Thaw ID columns created and populated..

 

thx

jake

 

Starting Table:

Sample ID	Thaw Date	Thaw ID
1	7/28/03	43
2	7/28/03	12
3	7/28/03	32
4	7/28/03	54
4	9/28/03	78
4	11/28/03	95
5	7/28/03	58
5	9/28/03	80
6	7/28/03	60

 

Desired Output Table:

Sample ID	Thaw Date 1	Thaw ID 1	Thaw Date 2	Thaw ID 2	Thaw Date 3	Thaw ID 3
1	7/28/03	43
2	7/28/03	12
3	7/28/03	32
4	7/28/03	54	9/28/03	78	11/28/03	95
5	7/28/03	58	9/28/03	80
6	7/28/03	60

Re: Table rearrangement challengeThank you so much for your answer- it worked perfectly...

I am not using this for an analysis- rather to reorganize a large database of stored samples to upload into a commercial freezer database that requires a specific format... I always turn to JMP for this type of work, but couldn't figure this one out.. I will certainly read up on the Col Rank function as this was the step I had not considered...

best,
jake

Re: Table rearrangement challengethanks so much... this got me very, very close to the desired output.. but the reply below (from jerry_cooper) got me all the way there...

I very much appreciate you taking the time to think about the problem and providing your input....

best,
jake
Re: Table rearrangement challenge

Hi @jdkurtis ,

It might be helpful to know what analysis you're hoping to do, as it is very likely that your data are already in an analysis-ready format. Most JMP platforms play very nicely with "tall" or "stacked" data. However, the easiest way to do what you are asking is to create a new formula column containing the rank for each date within Sample ID:

Col Rank( :Thaw Date, :Sample ID )

 

Then just use the Tables->Split function with the following roles defined:

Split By: The new formula column

Split Columns: Thaw ID, Thaw Date

Group: Sample ID

 

This should give you what you're after. Hope this helps.

Re: Table rearrangement challenge

Once you have done your Summary on the data table, then you can use

     Tables==>Split

and Split By Thaw Date, Group on Sample ID, and having Thaw ID as the Split Columns