214427_en-US

liliconcom

Re: Matching values and coding them

Here is one way to solve your first question.  I am using matrix operations in an attempt to cut down on the number of comparisons you would have to do, if you marched down each column for each row making a comparison for each cell.  Others may have better ways to do this:

Names Default To Here( 1 );
dt = New Table( "Example",
 Add Rows( 2 ),
 New Column( "Jan", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [11111, 33333] ) ),
 New Column( "Feb", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [22222, 33333] ) ),
 New Column( "March", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [11111, 44444] ) ),
 New Column( "April", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [11111, 55555] ) ),
 New Column( "May", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [11111, 11111] ) ),
 New Column( "June", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [22222, 33333] ) ),
 New Column( "July", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [11111, 22222] ) ),
 New Column( "August",
  Numeric,
  "Continuous",
  Format( "Best", 12 ),
  Set Values( [22222, 11111] ),
  Set Display Width( 51 )
 )
);
For( k = 1, k <= N Rows( dt ), k++,
 theRow = dt[k, 0];
 compare = theRow[1];
 theRow = theRow - compare;
 theLoc = Loc( theRow );
 theRow = theRow + 1;
 For( i = 1, i <= N Rows( theLoc ), i++,
  theRow[theLoc[i]] = 0
 );
 theRow[1] = compare;
 dt[k, 0] = theRow;
);

Concerning your second question......I am a little confused on what you are talking about.  Could you please show a simple example of what your data looks like now, and what you would want it to end up as?

Re: Matching values and coding them

welcome @Apurva14 

have a look at this solution.

please let us know if it is in the right direction

 

New Table( "Untitled",
 Add Rows( 4 ),
 New Column( "Jan", 
  
  Set Values( [11111, 33333, 44444, 55555] )
 ),
 New Column( "Feb ", 
  
  Set Values( [22222, 33333, 11111, 55555] )
 ),
 New Column( "March", 
  
  Set Values( [11111, 44444, 22222, 11111] )
 ),
 New Column( "April ", 
  
  Set Values( [11111, 55555, 33333, 33333] )
 ),
 New Column( "May ", 
  
  Set Values( [11111, 11111, 44444, 55555] )
 ),
 New Column( "June", 
  
  Set Values( [22222, 33333, 44444, 55555] )
 ),
 New Column( "July ", 
  
  Set Values( [11111, 22222, 22211, 55555] )
 ),
 New Column( "August", 
  
  Set Values( [22222, 1111, 44444, 22222] )
 )
);
 

// here is where the work starts 
dt = Current Data Table();

cols = dt << get column names( string );
ncols = N Items( cols );
For( i = 1, i <= ncols, i++,
 Eval( Parse( "dt << new column (cols[i+1], Formula(" || Char( cols[1] ) || " ==" || Char( cols[i + 1] ) ) )
);
 

best,

Ron

 

Re: Matching values and coding them

with respect to your second question try the following:

New Table( "Untitled",
 Add Rows( 4 ),
 New Column( "Jan", Set Values( [11111, 33333, 44444, 55555] ) ),
 New Column( "Feb ", Set Values( [22222, 33333, 11111, 55555] ) ),
 New Column( "March", Set Values( [11111, 44444, 22222, 11111] ) ),
 New Column( "April ", Set Values( [11111, 55555, 33333, 33333] ) ),
 New Column( "May ", Set Values( [11111, 11111, 44444, 55555] ) ),
 New Column( "June", Set Values( [22222, 33333, 44444, 55555] ) ),
 New Column( "July ", Set Values( [11111, 22222, 22211, 55555] ) ),
 New Column( "August", Set Values( [22222, 1111, 44444, 22222] ) )
);

// here is where the work starts
dt = Current Data Table();

dt2 = dt <<  Stack(
 columns( :Jan, :Feb, :March, :April, :May, :June, :July, :August ),
 Source Label Column( "Label" ),
 Stacked Data Column( "Data" )
);

dt3 = dt2 << Summary(
 Group( :Data ),
 Freq( "None" ),
 Weight( "None" )
);

dt2 << Update(
 With( dt3 ),
 Match Columns( :Data = :Data )
);
dt3 << close window (no save);

dt2 << Sort( By( :N Rows ), Order( Ascending ) , replace table);

// at this point the unique values should be at the top of the table

 

best,

Ron

Matching values and coding them

So I have many same values(eg;-12345) in multiple columns by months I wanted to keep the values in the first column as numbers and code the rest if present or absent as 0 and 1. 

 

One more thing about the data I have is that some values are unique but they are not in the first column how can stack them above one another and still code them by month. 

 

 

this what my data looks like 

Jan Feb  March April  May  June July  August 11111 22222 11111 11111 11111 22222 11111 22222 33333 33333 44444 55555 11111 33333 22222 1111 and so on                                                                              

 

say for example 

111111 0 1 1 0 1 0

I want it to look like these !! 

any help is appreciated @staff