Combining two strings

[#1]

Hi,

I have 2 columns that contain strings in the following format:

string1 = 1;0;2;0;0;8;0
string2 = 0;1;0;3;5;0;13

Note 1: Both strings contain the same number of ";" (but they are not necessarily the same length).

Note 2: The two strings have 0's in alternating positions.

Note 3: A string can contain two 0's in a row (separated by ";") as long as the other string have two non-zero terms in a row at the same positions.

I want to create a new column (string3) which is the result of merging the two columns without the 0's. For the example above:

string3 = 1;1;2;3;5;8;13

So the string3 should have the same number of ";" as string1 and string2, and must contain all the terms between the ";" by order, with no 0's.

Is there a way to do it using simple formulas only? Alternatively, using other methods such as loops?

Please see attached file.

Thanks,

Yotam 

[#2 · Accepted solution]

Here is a formula that will work.  I have attached a new data table with a new formula column

w = 1;
newString = "";
While( Word( w, :string1, ";" ) != "",
	If( newString != "",
		newString = newString || ","
	);
	If( Word( w, :string1, ";" ) != "0",
		newString = newString || Word( w, :string1, ";" ),
		newString = newString || Word( w, :string2, ";" )
	);
	w++;
);
newString;

The formula does not check for the situation where zeros are found in the same location on both strings, nor for the situation where non zeros might appear in the same position on both strings.  No rules were defined as to how to handle such situations.  If such issues need to be checked on, modification of the formula will be a simple matter. 

[#3]

Fantastic.

Thanks!

[#4]

@DonMcCormack- I think this might be a great challenge problem.

• how to catch and deal with errors in the strings (non-numbers, mismatched zeros, not same length)
• how to make it fast for 1E6 semicolons

edit: Using the JMP 16 For Each Across loop:

string1 = "1;0;2;0;0;8;0";
string2 = "0;1;0;3;5;0;13";

result = "";
For Each( {{w1, w2}, idx}, across( Words( string1, ";" ), Words( string2, ";" ) ),
    result ||= (If( idx > 1, ",", "" ) || If( w1 == "0", w2, w1 ))
);
result;

"1,1,2,3,5,8,13"

[#5]

Thanks!

Unfortunately I currently don't have JMP16 yet, but this solution sure looks very elegant!

[#6]

While @txnelson and @Craige_Hales gave you some interesting and useful options, if I've understood your situation correctly, it can be done in a formula without loops. 

Here's the formula I ended up with, first as a picture and then in JSL (which can be pasted into the Formula Editor).

If( !Is Missing( :string1 ) & !Is Missing( :string1 ),
	string1list = Words( :string1, ";" );
	string2list = Words( :string2, ";" );
	Concat Items( string1list[Loc( string1list, "0" )] = string2list[Loc( string1list, "0" )], ";" );
,
	""
)

This uses the Words() function to convert your strings to lists. It uses the Loc() function to return a matrix of locations of the 0s in string1 and uses that as a index for both string1list (on the left side of an assignment) and string2list (on the right side of that assignment). Finally, the Concat Items() function converts the list back into a semicolon delimited string.

If any of this is confusing or doesn't make sense, let me know and I'll explain further.

[#7]

I see this already has a few solutions but since you used Fibonacci numbers you got my attention

Here is another formula that operates on the strings directly (assuming they are stored in columns named string1 and string2, respectively) by converting them to matrices, adding the matrices, and converting the result back to a string.

Cheers,

Brady

Substitute( Char( Parse( "[" || Substitute( :string1, ";", "," ) || "]" ) + Parse( "[" || Substitute( :string2, ";", "," ) || "]" ) ),
	"[", "",
	"]", "",
	",", ";",
	" ", ""
);

Also, I'm not sure whether it is feasible for you to use comma separators instead of semicolons, but if this is done, the formula becomes a little simpler.

Substitute( Char( Eval( Parse( Eval Insert( "[^:string1^] + [^:string2^]" ) ) ) ),
	"[", "",
	"]", "",
	" ", ""
)