738244_en-US

liliconcom

expression column with list compare to given list to replace with desired value

Hi,

is there a method through add new formula to column for issue below?

I have a column that is list value and I want to compare to given list, if element from list in column match to given list then replace that single element with "1". Below are example. The given list is {"aa", "app", "b", "pp", "zzzz"}

 

 

Automation and ScriptingRe: expression column with list compare to given list to replace with desired value

Below is one idea you could use

Names Default To Here(1);

dt = New Table("Untitled",
	Add Rows(1),
	Compress File When Saved(1),
	New Column("Column 1", Expression, "None", Set Values({{"aa", "bb", "dd"}, {"bb", "2", "cc"}}))
);

mylist = {"aa", "bb", "cc"};

dt << New Column("res", Expression, "None");

For Each Row(dt,
	rowresult = Transform Each({myitem, idx}, mylist,
		If(:Column 1[Row()][idx] == myitem,
			"1"
		,
			myitem
		)
	);
	:res = rowresult;
);

Re: expression column with list compare to given list to replace with desired value

still having issue. The suggestion will only match first element in mylist to first element in :Column 1. Then second to second and third to third. Also if :column 1 has more than 3 elements, the rest will be removed. Below is the result from suggestion

how to enable below.

 

Re: expression column with list compare to given list to replace with desired value

There was one mistake in my example and I understood incorrectly what you were looking for. This is most likely closer to what you are looking for

Names Default To Here(1);

dt = New Table("Untitled",
	Add Rows(1),
	Compress File When Saved(1),
	New Column("Column 1",
		Expression,
		"None",
		Set Values({{"aa", "bb", "dd"}, {"bb", "2", "cc"}})
	)
);

mylist = {"aa", "bb", "cc"};

dt << New Column("res", Expression, "None");

For Each Row(
	dt,
	rowresult = Transform Each({item}, :Column 1[Row()],
		If(Contains(mylist, item),
			"1"
		,
			item
		)
	);
	:res = rowresult;
);