392962_en-US

liliconcom

Combining two strings

Hi,

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

string1 = 1.11;0;3;0;5

string2 = 0;2.2;0;4;0

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.

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: 1.11;2.2;3;4;5

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 formulas only (without jsl scripting)?

Alternatively, is there a way to do it with jsl?

Please see attached file.

Thanks,

Yotam 

Data TableScriptingRe: Combining two strings

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;

Re: Combining two strings

@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"

Re: Combining two strings

Fantastic.

Thanks!

Re: Combining two strings

Thanks!

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

Re: Combining two strings

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.