270116_en-US

liliconcom

Re: merging columns

1. Stack all of the columns
2. I assume you then delete all rows that are marked "NON DETECTED"
3. Sort the data by Subject, so all of a subjects rows with Viruses detected are together
4. Create a new column which is a sequence number for each subject using this formula

   If( Row() == 1 | :Subject != Lag( :Subject ),
    :Virus# = 1,
    :Virus# = Lag( :Virus# + 1 )
   )

5. Use Tables==>Split
   1. Split By Virus#
   2. Group by   Subject
   3.  Split Column  Label

merging columns

I have a table with different viruses in different columns ( with rows description of detected/non detected)

 

I need them to be in one column with virus names in rows.

 

I tried to stack them. However, some patients have more than one virus. I would ideally want them to be in a columns like Virus 1 ( in majority of patients there would be only one virus), Virus 2 ( if there are two virus) , Virus 3 ( if there are three virus)

 

Thank you

 

Data TableRe: merging columns

The new column you create needs to be called "Virus#", then just paste the formula into the formula Editor.

Re: merging columns

I was able top get to step4 pretty easily but am stuck at the formula.  I am assuming simply copying and pasting formula would not work!

 

 

 

Re: merging columns

another screen shot of the formula editor

Re: merging columns

You referred to Subjects, so I assumed the name of the column that has the subjects name of ID was called "Subject".  Apparently that is not the case.  It appears that the column MRN is the column, but you refer to it as Mr No in the jpg you posted

 

I have modified my initial response to give more specifics that should provide you with your answers.

Re: merging columns

  

Hmm..did not work

  

 

 

Re: merging columns

awesome. Do not totally understand but it worked !

 

Now I have two worksheets which just needs to be combined

Re: merging columns

1. I am glad it worked
2. You need to study the steps that were used to get your task done, so the next time you will be able to independently complete the task....or at least get further along.....the Discussion Community is here to help you learn about JMP, not here to write code
3. You do not have 2 worksheets.  Excel has worksheets, JMP has data tables.  And they are not two different names for the same thing.  Because Excel is "Cell" based, and the worksheet is not just the data storage device, but also the only display vehicle, one stuffs a variety of different kinds of data and information into a worksheet.  On the other hand, JMP's data table is a column based analytical data structure, and uses independent display objects for presenting the information.  The bottom line is, be careful of combining different data structures into one JMP data table.
4. That aside, You can use    Tables==>Join       or Tables==>Update      to put your data together