Choose Language Hide Translation Bar
Highlighted
Apurva14
Level II

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 

JanFeb MarchApril May JuneJuly August
1111122222111111111111111222221111122222
333333333344444555551111133333222221111
and so on       
        
        
        
        

 

say for example 

111111011010

I want it to look like these !! 

any help is appreciated @staff

AD14
3 REPLIES 3
Highlighted
ron_horne
Super User

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

 

Highlighted
txnelson
Super User

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?

Jim
Highlighted
ron_horne
Super User

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

Article Labels

    There are no labels assigned to this post.