Replace characters in the string

[#1]

Hi JMP community,

I'm trying to write a jsl code that would take a column with a string in it, and in each row, replace the count with the values in the count column. The final results should look something like in the Results column

Any Suggestions?

[#2 · Accepted solution]

The strings appear to follow the same pattern. Maybe use the words() function with either a space or " as the separator. Then use concatitems() to put it back together.

[#3]

Regex is one other option

Regex(:Results, "(BIN = \!".+\!") (\d+)(\!".+)", "\1 " || Char(:Count) || "\3")

but in this case I would use Words() + Concat Items() like Craige suggested (if the pattern is always same)

[#4]

Thanks Craig and Jarmo 

[#5]

@jthi 
I used Cragis logic to update the count in the string but it doesn't replaces the correct count string. I'm not sure what I am doing wrong. Can you take a look at my code?

Assigns incorrect counts 

Here's the code:

dt = Current Data Table();
/// Get list of all the bins
binxx = Associative Array( :Bins[23:: N Rows( dt )] ) << Get Keys;

delcols = {"", "	", "}"};
/// Remove garbage values																	
For( i = 1, i <= N Items( delcols ), i++,
	Remove From( binxx, As List( Loc( binxx, delcols[i] ) ) );
	
	
);

///Get rows where Bins column contains BIN = 
rows = dt << get rows where( Contains( :Bins, "BIN = " ) );
total = {};

//Loop to calculate the count of each bins
For( i = 1, i <= N Items( binxx ), i++, 
	
	
	Insert Into( total, N Items( dt << get rows where( :Bins == binxx[i] ) ) )
);
//create array to store bin and counts
arra = Associative Array( binxx, total );

//loop to modify the character count in the table

For( j = 1, j <= N Items( binxx ), j++,
	For( i = 2, i <= N Items( rows ), i++, 
	
		If( Contains( arra, Substr( :Bins[i], 8, 1 ) ), 
		
			x = Words( :Bins[rows[i]] );
			x[4] = Char( arra[binxx[j]] ); 

			:Bins[rows[i]] = Concat Items( x );

		)
	)
);

[#6 · Accepted solution]

I think the issue is with this line

x[4] = Char( arra[binxx[j]] ); 

I would modify the loop to loop over the rows instead of associative array + rows:

Names Default To Here(1);

dt = Current Data Table();
/// Get list of all the bins
binxx = Associative Array(:Bins[23 :: N Rows(dt)]) << Get Keys;

delcols = {"", "	", "}"};
/// Remove garbage values																	
For(i = 1, i <= N Items(delcols), i++,
	Remove From(binxx, As List(Loc(binxx, delcols[i])));
);

///Get rows where Bins column contains BIN = 
rows = dt << get rows where(Contains(:Bins, "BIN = "));
total = {};

//Loop to calculate the count of each bins
For(i = 1, i <= N Items(binxx), i++, 
	Insert Into(total, N Items(dt << get rows where(:Bins == binxx[i])))
);

//create array to store bin and counts
arra = Associative Array(binxx, total);

//loop to modify the character count in the table
For(i = 1, i <= N Items(rows), i++, 
	x = Words(:Bins[rows[i]]);
	curbin = Substitute(x[3], "\!"", "");
	If(Contains(arra, curbin),
		x[4] = Char(arra[curbin]);
		:Bins[rows[i]] = Concat Items(x);
	,
		continue();
	);
);

[#7]

Thanks a lot Jarmo