Our World Statistics Day conversations have been a great reminder of how much statistics can inform our lives. Do you have an example of how statistics has made a difference in your life? Share your story with the Community!
Choose Language Hide Translation Bar
Highlighted
Thomas1
Level V

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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
txnelson
Super User

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

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;
Jim

View solution in original post

6 REPLIES 6
Highlighted
txnelson
Super User

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

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

Jim
Highlighted
Thomas1
Level V

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

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() /*###*/"

Highlighted
txnelson
Super User

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

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.

 

 

Jim
Highlighted
Thomas1
Level V

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

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?

 

 

 

 

Highlighted
txnelson
Super User

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

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;
Jim

View solution in original post

Highlighted
Thomas1
Level V

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

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.

 

 

Article Labels

    There are no labels assigned to this post.