BookmarkSubscribeRSS Feed
Choose Language Hide Translation Bar
DamionSparhawk

Contributor

Joined:

Jul 13, 2018

Select, Delete, and Color

Okay new project, I still have some unfinished questions on the last one, but they're more aesthetic than priority. Anyway, here's the task; I have a excel sheet that contains a log of all events for a week, I would like to delete all the events that are not pertinent to the task, and then color other rows that contain common known issues or events relating to issues so they're easy to pick out of the lot. Column three contains the event data, column 1 contains the tool data, if possible I'd love to have the remaining data broken down by tool but one step at a time!

 

I browsed around and found a script that seemed like it might handle part of the process, deleting the known variables, but it isn't working. So... here's what I got.

Names Default To Here( 1 );

dt = Open ();

column_list = {"Alarm ID"
};

For( i = N Items( column_list ), i > 0, i--,
	dt << Select Where( as column(column_list[i]) ="'00000018", "'0000000E", "'000000B9", "'000000C1", "'000000CC", "'000000DF", "'000000E2", 
	"'0000023B", "'00000D15", "'00000D16", "'00000D1A", "'00000DEF", "'00000DF0", "'00000DF3", "'00000E6A", 
	"'00000E6D", "'00000EB2", "'00000ED6", "'00000F1A" );
	dt << delete rows;
);

Thanks in advance!

 

1 ACCEPTED SOLUTION

Accepted Solutions
txnelson

Super User

Joined:

Jun 22, 2012

Solution

Re: Select, Delete, and Color

The syntax of the Where Clause is incorrect.  It should be:

Names Default To Here( 1 );

dt = Open ();

column_list = {"Alarm ID"
};

For( i = N Items( column_list ), i > 0, i--,
	dt << Select Where( 
	as column(column_list[i]) == "'00000018" | 
	as column(column_list[i]) == "'0000000E" | 
	as column(column_list[i]) == "'000000B9" | 
	as column(column_list[i]) == "'000000C1" | 
	as column(column_list[i]) == "'000000CC" | 
	as column(column_list[i]) == "'000000DF" | 
	as column(column_list[i]) == "'000000E2" | 
	as column(column_list[i]) == "'0000023B" | 
	as column(column_list[i]) == "'00000D15" | 
	as column(column_list[i]) == "'00000D16" | 
	as column(column_list[i]) == "'00000D1A" | 
	as column(column_list[i]) == "'00000DEF" | 
	as column(column_list[i]) == "'00000DF0" | 
	as column(column_list[i]) == "'00000DF3" | 
	as column(column_list[i]) == "'00000E6A" | 
	as column(column_list[i]) == "'00000E6D" | 
	as column(column_list[i]) == "'00000EB2" | 
	as column(column_list[i]) == "'00000ED6" | 
	as column(column_list[i]) == "'00000F1A" );
	dt << delete rows;
);
Jim
3 REPLIES 3
txnelson

Super User

Joined:

Jun 22, 2012

Solution

Re: Select, Delete, and Color

The syntax of the Where Clause is incorrect.  It should be:

Names Default To Here( 1 );

dt = Open ();

column_list = {"Alarm ID"
};

For( i = N Items( column_list ), i > 0, i--,
	dt << Select Where( 
	as column(column_list[i]) == "'00000018" | 
	as column(column_list[i]) == "'0000000E" | 
	as column(column_list[i]) == "'000000B9" | 
	as column(column_list[i]) == "'000000C1" | 
	as column(column_list[i]) == "'000000CC" | 
	as column(column_list[i]) == "'000000DF" | 
	as column(column_list[i]) == "'000000E2" | 
	as column(column_list[i]) == "'0000023B" | 
	as column(column_list[i]) == "'00000D15" | 
	as column(column_list[i]) == "'00000D16" | 
	as column(column_list[i]) == "'00000D1A" | 
	as column(column_list[i]) == "'00000DEF" | 
	as column(column_list[i]) == "'00000DF0" | 
	as column(column_list[i]) == "'00000DF3" | 
	as column(column_list[i]) == "'00000E6A" | 
	as column(column_list[i]) == "'00000E6D" | 
	as column(column_list[i]) == "'00000EB2" | 
	as column(column_list[i]) == "'00000ED6" | 
	as column(column_list[i]) == "'00000F1A" );
	dt << delete rows;
);
Jim
DamionSparhawk

Contributor

Joined:

Jul 13, 2018

Re: Select, Delete, and Color

Awesome, yeah that works unsurprisingly, okay so that helps immensely, I'll play around with this and see if I can't figure out the rest, thanks!

Highlighted
DamionSparhawk

Contributor

Joined:

Jul 13, 2018

Re: Select, Delete, and Color

Okay, back again, I got some results, however I have a problem that is causing my brain to ache a bit more than my foot.

dt << New Column ( "Row State 1",
	Row State,
	Set Formula ( If( :Alarm ID == "'00000D06", Color State(70))),
);

this bit works to set the marker color of a specific row containing the ID I want, however when I start multiplying it, it stops being able to set the row state color itself. It does generate the Row State in the side tab, and I can apply the states individually, but obviously I'm trying to reduce the amount of manual input, so does anyone know a more efficient way? I've got a metric ton of values to input but here's the snippet I've put in so far.

 

Names Default To Here( 1 );

dt = Open ();

column_list = {"Alarm ID"
};

For( i = N Items( column_list ), i > 0, i--,
	dt << Select Where( 
	as column(column_list[i]) == "'00000018" | 
	as column(column_list[i]) == "'0000000E" | 
	as column(column_list[i]) == "'000000B9" | 
	as column(column_list[i]) == "'000000C1" | 
	as column(column_list[i]) == "'000000CC" | 
	as column(column_list[i]) == "'000000DF" | 
	as column(column_list[i]) == "'000000E2" | 
	as column(column_list[i]) == "'0000023B" | 
	as column(column_list[i]) == "'00000D15" | 
	as column(column_list[i]) == "'00000D16" | 
	as column(column_list[i]) == "'00000D1A" | 
	as column(column_list[i]) == "'00000DEF" | 
	as column(column_list[i]) == "'00000DF0" | 
	as column(column_list[i]) == "'00000DF3" | 
	as column(column_list[i]) == "'00000E6A" | 
	as column(column_list[i]) == "'00000E6D" | 
	as column(column_list[i]) == "'00000EB2" | 
	as column(column_list[i]) == "'00000ED6" | 
	as column(column_list[i]) == "'00000F1A" );
	dt << delete rows;


);

dt << New Column ( "Row State 1",
	Row State,
	Set Formula ( If( :Alarm ID == "'00000D06", Color State(70))),
);
dt << New Column ( "Row State 2",

Row State,
Set Formula ( If( :Alarm ID == "'000000B2", Color State(6))),
);
dt << New Column ( "Row State 3",

Row State,
Set Formula ( If( :Alarm ID == "'000000BE", Color State(6))), 
);

dt << New Column ( "Row State 4",

Row State,
Set Formula ( If( :Alarm ID == "'0000013C", Color State(6))),
);

dt << New Column ( "Row State 5",

Row State,
Set Formula ( If( :Alarm ID == "'00000D3B", Color State(73))),
);