Hi everyone, I want to know if there is a way to assign the value ordering labels of one column based in another column that would have a date sorted ascending via scripting. Here is a little example of the columns. I want to have my ID's order by date. IDDate71220G101/04/201880102G101/05/201880209G102/19/2018 I need that beacuse I need to do a graphic in Control Chart Builder by ID, but to see the trend in time also. I know if build a XBar chart by itself it would accomodate my values as they appear in the data table, but Control Chart Builder won't. Is a big data table so any jsl code for doing this would be great. Thanks in advance.

nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Value Ordering of a column based in Ascending Date for another column

nicoleaba

Hi everyone,

I want to know if there is a way to assign the value ordering labels of one column based in another column that would have a date sorted ascending via scripting.

Here is a little example of the columns. I want to have my ID's order by date.

ID	Date
71220G1	01/04/2018
80102G1	01/05/2018
80209G1	02/19/2018

I need that beacuse I need to do a graphic in Control Chart Builder by ID, but to see the trend in time also. I know if build a XBar chart by itself it would accomodate my values as they appear in the data table, but Control Chart Builder won't.

Is a big data table so any jsl code for doing this would be great.

Thanks in advance.

Accepted answers

pmroz

This code will work:

dt = New Table( "Test", Add Rows( 3 ), 
		New Column( "ID", Character, "Nominal",
		Set Values( {"71220G1", "80102G1", "80209G1"} )
	),
	New Column( "Date", Numeric, "Continuous", Format( "m/d/y", 12 ),
		Input Format( "m/d/y" ),
		Set Values( [3597868800, 3597955200, 3601843200] )
	)
);
dt << sort(replace table(1), by(:Date));
id_values = dt:ID << get values;
dt:id << Set Property( "Value Ordering", id_values );

If you have multiple dates for a given ID you'll have to take a different approach.

All comments

uday_guntupalli

@nicoleaba,

Sorting the table by dates could work. So something like this:

dt = Current Data Table(); 

dt1 = dt << Sort( By( :Date ), Order( Ascending ) );

pmroz

This code will work:

dt = New Table( "Test", Add Rows( 3 ), 
		New Column( "ID", Character, "Nominal",
		Set Values( {"71220G1", "80102G1", "80209G1"} )
	),
	New Column( "Date", Numeric, "Continuous", Format( "m/d/y", 12 ),
		Input Format( "m/d/y" ),
		Set Values( [3597868800, 3597955200, 3601843200] )
	)
);
dt << sort(replace table(1), by(:Date));
id_values = dt:ID << get values;
dt:id << Set Property( "Value Ordering", id_values );

If you have multiple dates for a given ID you'll have to take a different approach.

nicoleaba

It worked perfectly!

Thank you so much @pmroz.