345005_en-US

liliconcom

split a column and create multiple multiple based on split using JSL

Hello, 

 

I need to read existing table and find the column of interest [say the column name is "data"]. 

the column "data" is numeric with "dot" delimited and have same length. 

My goal is to open this table, read column "data" , remove the "dot" and create new columns [as per the length of value] and each column holds each value.

 

Orig table:

data

------

123.56.8.342

431.02.0.567

318.00.1.816

.....

 

expected output:

data                   c1   c2   c3   c4   c5   c6   c7   c8   c9

------------------------------------------------------------------------------

123.56.8.342      1   2     3     5     6     8     3     4      2    

431.02.0.567      4   3     1     0     2     0     5     6      7

318.00.1.816      3    1    8     0     0     1     8     1      6

 

In my first attempt [trial1.jmp] , where I have 2 for loops [inner loop for creating cols, and other loop for # of rows],  it is creating more columns [ie width of the data read * no, of rows]. 

in my second attempt [trail2.jmp], I am getting the right # of cols, but values static. 

attached is my jsl's.

Appreciate the help, burning the midnight oil on this:(

 

Thanks,

David

 

Data TableRe: split a column and create multiple multiple based on split using JSL

I only checked the version 2 of .jsl and it was quite close.

I have attached modified version.

 

I modified the loop part mostly. There is most likely way to manage without Eval(Parse()) with expressions but I went with this solution this time. You were using data from variable clean_DATA which stays the same for all rows. Also if you need to have formulas you will have to use Eval(Parse()) (or expressions?) to keep formulas working in the datatable. If no formulas are needed I would go with << Set Each Value instead of Formula.

For(i = 1, i <= ncols_to_add, i++, // the || operator joins two strings
 Eval(Parse("dt << New Column(\!"c\!" || Char(i),Numeric, Continous, 
  Formula(Num(Substr(Regex(:data, \!"\D\!", \!"\!", GLOBALREPLACE ), "||char(i)||", 1 )));
 )));")); // put 1 bit in each column
);

With << Set Each Value the for loop would look like this:

For(i = 1, i <= ncols_to_add, i++, // the || operator joins two strings
 dt << New Column("c" || Char(i),Numeric, Continous, 
  << Set Each Value(Num(Substr(Regex(:data, "\D", "", GLOBALREPLACE ), i, 1 )));
 )
); // put 1 bit in each column

Re: split a column and create multiple multiple based on split using JSL

Here is how I would accomplish your request.  The Substitute() function allows for the formula to have the actual value of variable "i" to be in the formula rather than a reference to variable "i"

Names Default To Here( 1 );

dt = Current Data Table();

col = Column( dt, "DATA" );

colLength = Length( col[1] );

For( i = 1, i <= colLength, i++,
 If( Substr( col[1], i, 1 ) != ".",
  Eval(
   Substitute(
     Expr(
      dt << New Column( "c" || Char( i ), Numeric, Continous, Formula( Num( Substr( :data, __i__, 1 ) ) ) )
     ),
    Expr( __i__ ), i,

   )
  )
 )
);

Re: split a column and create multiple multiple based on split using JSL

Thanks Jim. 

you have  elegant solution. 

based on your proposed method, I noticed see the col name are not continues, [ie. skips the Col # when ever "dot" is encountered]; little column names massaging is needed [which I took care]. 

 

Question:

Would you suggest to have column have formula and use eval/substitute or do it with "set each value" as Jamo suggested from the performance standpoint? 

 

Thanks,

David