54224_en-US

liliconcom

Column manipulations

I have tables that have data in the following order:

I want all the tables to look like this:

 

How can I achieve this in jsl? 

Re: Column manipulations

@Aam_jmp, 
      In your specific example, the following would work: 

 

Clear Log(); Clear Globals(); 

dt = Current Data Table();  
dt2 = (dt << Tabulate(
      Set Format( N( 9, "Best" ) ),
      Add Table(
       Column Table( Analysis Columns( :Age, :Height, :Weight ) ),
       Column Table( Grouping Columns( :Result ) ),
       Row Table( Grouping Columns( :Name ) )
         )
          )
   ) << Make Into Data Table; 

dt2 << New Column("Result",Character,Nominal,Formula(If(:Fail==1,"Fail",If(:Pass==1,"Pass")))); 

However, you will have to adjust the last column every single time in this approach. 
A more generalized approach is shown below: 

 

Clear Log(); Clear Globals(); 

dt = Current Data Table(); 
CharValsAA = Associative Array(dt:Result); 
Results = CharValsAA << Get Keys; 

for(i = 1, i < N Items(Results), i++,
  If(!IsMissing(Results[i]),
   ,
   Remove From(Results,i);
    );
   );

dt2 = (dt << Tabulate(
      Set Format( N( 9, "Best" ) ),
      Add Table(
       Column Table( Analysis Columns( :Age, :Height, :Weight ) ),
       Column Table( Grouping Columns( :Result ) ),
       Row Table( Grouping Columns( :Name ) )
         )
          )
   ) << Make Into Data Table; 
nR = N Rows(dt2);

// Short sighted approach 
//dt2 << New Column("Result",Character,Nominal,Formula(If(:Fail==1,"Fail",If(:Pass==1,"Pass")))); 

// Generalized Approach 
dt2 << New Column("Test",Character,Nominal, Set Each Value("a")); 
for(i = 1, i <= N Items(Results), i++,
  VarName = Results[i]; 
  Eval(Parse(Eval Insert("\[ Temp_^i^ = Loc(dt2:^VarName^ << Get Values)  ; ]\" ))); 
  Eval(Parse(Eval Insert("\[ for(r = 1, r <= N Rows(Temp_^i^), r++,
           If(Temp_^i^[r] > 0,
           dt2:Test[Temp_^i^[r]] = Results[^i^];
             );
            ) ; ]\" ))); 
   );

Re: Column manipulations

Well, thats my only objective. To make the tables I get not have repetitive names in the column. 

Re: Column manipulations

@Aam_jmp,
         Owing to the nature of the data being character and not numeric, I wonder if this is possible in a straightforward way . 

         One way I can think of doing it is - by mapping each unique value of the character column, tabulating using the numeric equivalent and remap to the character values. I don't know if you would want to go that route and unnecessarily complicate it. 

          If you maybe shared your entire objective, perhaps there might be a better way to handle this ? 

Re: Column manipulations

@uday_guntupalli I have already tried doing this. This seems to make a new column for every value that is present in the column. I would like to know if there is any option in JMP that would help me acquire something like this:

 

Re: Column manipulations

@Aam_jmp, 

      I would think Tabulate can still work. If you had a new column "Test" and Tabulated you would end up with something like this : 

 

dt = Current Data Table(); 
dt1 = (dt << Tabulate(
 Set Format( N( 9, "Best" ) ),
 Add Table(
  Column Table( Analysis Columns( :Age, :Height, :Weight ) ),
  Column Table( Grouping Columns( :Test ) ),
  Row Table( Grouping Columns( :Name ) )
 )
)) << Make Into Data Table

 If this doesn't address what you are asking, kindly show what your data looks like (like a sample) - so I can see where I am off. 

Re: Column manipulations

@uday_guntupalli What if one of the columns I have has character data type?

Re: Column manipulations

@Aam_jmp, 
             Use Summarize or tabulate 

dt = Current Data Table();

dt1 = (dt << Tabulate(
 Add Table(
  Column Table( Analysis Columns( :Age, :Height, :Weight ) ),
  Row Table( Grouping Columns( :Name ) )
 )
)) << Make Into Data Table