How to create a indicator column for double data with a formula

[#1]

I' dlike to create a formula indicator column for double data by applying the following JSL Code:

dt << Clear Column Selection();
dt << Select Duplicate Rows();
dt << Name Selection in Column( Selected( 1 ), Unselected( 0 ) );
dt << Clear Select();

Despite I 'm getting the right results, the code creates a lot error messages. It would be great if the code could be improved.

[#2]

Your code looks good......what errors are you getting?

[#3]

First

Within the formula editor the the first Code line is marked with the red line.

Second

In case the formula is being applied, a pop up window appears with the messages like "Send Expects Scriptable Objects ...; Send, dt << /*###*/" or "Selection() /*###*/"

[#4]

OK......my previous comment was based upon the assumption that the code was being run in open code, rather than as a formula.  Using this as a formula, there are some concerns.

1. The pointer to the data table, dt,  must be defined in the formula, if you are going to reference it.
2. You are using functions that automatically move through all rows when executed.  Formulas also loop across all rows in a data table and execute the formula once for each row.  Therefore, using the code the way you are doing it means that you are running the  "Select Duplicate Rows" over and over again, when in actuallity, you only need to execute it once.
3. The red line for your first line in the fomula is really a warning, not an error.

Below is a version of your code that I would use, if using this as a formula.

If( Row() == 1,
	dt = Current Data Table();
	dt << Clear Column Selection();
	dt << Select Duplicate Rows();
	dt << Name Selection in Column( Selected( 1 ), Unselected( 0 ) );
	dt << Clear Select();
);

I am not really clear on why you would want to put this into a formula column, since the column you place it in, will contain all missing values.......so you may want to rethink your process.

[#5]

Hi Jim,

thanks for your support. I tried to simplify my topic. My goal is to create a column in which the multiple data are addressed. I was able to do this with the following JSL:

Names Default To Here( 1 );
 
dt = Current Data Table();
dt << Select Columns( :Column 1, :Column 2);
dt << Select Duplicate Rows();
dt << Name Selection in Column(
       Column Name( "MultipleData" ),
       Selected( 1 ),
       Unselected( 0 ));
 
dt << Clear Select();

for multiple data from column 1 and 2, of the attached file. This works fine.  However, because the working files are frequently updated with new data via SQL, I would prefer to create a formula  Column "MultipleData".

Unfortunately I'm getting only zeros, if your last code is being applied. What could be a solution?

[#6 · Accepted solution]

I am hoping there is a community member that can solve your question better than the method that I used below.  Given what you want, there are conflicting issues in trying to use the selection capability of the data table, but then you want to clear the selections.  That would force the recalculation of the formula.  So what I have done, is to have the formula create a subset of the data table, perform your calculations on the subsetted data table( isolating the selections) and then reading the results from the subset back to the original data table....and then deleting the subsetted data table.  Take a look and see if you understand what I am doing.  It appears to work.

If( Row() == 1,
	dt = Current Data Table();
	dt << Select Columns( :Column 1, :Column 2 );
	dt2 = dt << subset(
		private,
		dt,
		selected rows( 0 ),
		selected columns( 1 )
	);
	dt2 << Select Duplicate Rows();
	dt2 << Name Selection in Column(
		Column Name( "MultipleData" ),
		Selected( 1 ),
		Unselected( 0 )
	);
);
x = dt2:MultipleData[Row()];
If( Row() == N Rows( dt ),
	dt << clear column selection;
	Close( dt2, nosave );
);
x;

[#7]

Hi Jim,

Thanks for the solution. I did test your code with a formula column, on a big  database (some 100k rows and 100 columns). It worked fine.

My solution is the following JSL code:

Names Default To Here( 1 );

dt = Current Data Table();

//Delete preveious version of the indicator column
dt << Delete Columns("MultipleData");

wait(2);

//Create the new  version of the indicator column
dt << Select Columns( :Column 1, :Column 2);
dt << Select Duplicate Rows();
dt << Name Selection in Column(
	Column Name( "MultipleData" ),
	Selected( 1 ),
	Unselected( 0 ));
	
dt << Clear Select();

dt << Clear Column Selection();

As I mentioned before,  a formula solution, like your code, is preferred. It is more convenient, in case the data set is updated.