- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
date
Hello everyone,
I have a database like this (this is a summary here):
This database comes from a script which works well so far, however the last column would allow calculating a last duration different from the others.
The values in this last column represent OPs which correspond to objects.
For example for the 1st line: the value 60 corresponds to the object 2041, we would need a formula allowing duration 2041 = end date 2041 - start date 2041 and so on for each line.
When the value is equal to 0 we could assign the value 0 to duration 2041.
And so on..
I started with the script below:
Names Default To Here(1);
dt = current data table();
If( !Is Missing(:"valeur ST1-2"n),
For Each Row(
idx = dt[row(), :"valeur ST1-2"n]
dt[idx, durée[idx]] = dt[idx, date fin[idx]] - dt[idx, date début[idx]];
),
0
);
This post originally written in French and has been translated for your convenience. When you reply, it will also be translated back to French .
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: date
Here is the script that I would use to accomplish what you want.
Names Default To Here( 1 );
dt = Current Data Table();
// Set the display format to a JMP Duration format
:"valeur ST1-2"n << format("hr:m:s");
// Loop through the rows and assign the new values
For Each Row(
If( :"valeur ST1-2"n != 0,
:"valeur ST1-2"n = :Date fin 2041 -
:Date début 2041,
:"valeur ST1-2"n = 0
)
);
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: date
Thank you for your reply !
This works for the 1st line but then the values in the “ST1-2 value” column change and therefore subsequently it is not the same duration that must be recalculated.
For example for the 3rd line: ST1-2 value = 82 and 82 corresponds to the object 2000 so we should make duration 2000 = end date 2000 - start date 2000 for the 3rd line
This is the aim of having here in the intermediate database an overview of the assignment of the OP with each object (columns 711-2041-9916 inside the corresponding OP).
The goal is to replace the duration columns, the “ST1-2 value” column will not be modified.
my script does not work correctly:
Names Default To Here(1);
dt14 = current data table();
dur_cols = Filter Each( {col_name}, dt14 << Get Column Names( "String", Continuous ),
Starts With( col_name, "Durée" )
);
fin_cols = Filter Each( {col_name}, dt14 << Get Column Names( "String", Continuous ),
Starts With( col_name, "Date fin" )
);
debut_cols = Filter Each( {col_name}, dt14 << Get Column Names( "String", Continuous ),
Starts With( col_name, "Date d" )
);
// for indexing later
num_colnr = Transform Each( {col_name}, dur_cols, Word( -1, col_name ) );
// Loop through the rows and assign the new values
For Each Row(
If( :"valeurs ST1-2"n != 0,
colidx = Loc(dt14[row(), num_colnr ] == :"valeurs ST1-2"n);
If( N items (colidx) > 0,
dt14[colidx,dur_cols] = Eval(dt14[colidx,fin_cols] - dt14[colidx,debut_cols]);
,
continue()
);
,
continue()
);
//show(colidx);
);
This post originally written in French and has been translated for your convenience. When you reply, it will also be translated back to French .
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: date
- How does one know that a value of 60 corresponds to 2041 and a value of 82 corresponds to 2000? And what is done with a valeur ST1-2 value of 68?
- The example data table that was provided does not have a column for a 2000 date.
- The JSL can easily be changed to use different calculations for different values of valeur ST1-2, if either the rules for the decision of which columns to use is discernable, or if a list of the valeur ST1-2 is provided, along with the columns to use for the different values.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: date
The database is reduced here, but for each value in the column ST1-2 values columns make it possible to make the link between the OP value and each object (in the reduced database these are columns 711-2041- 9916).
The value 60 corresponds to 2041 because in the 1st line:
So on for each line… for 82 which corresponds to 2000 it is just one more example but this will mean that on the second line the column 2000 would be filled by the value 82.
This post originally written in French and has been translated for your convenience. When you reply, it will also be translated back to French .
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: date
I think the below script gets you what you want, or at least close to it
Names Default To Here( 1 );
dt14 = Current Data Table();
Lookup = Filter Each( {col_name}, dt14 << Get Column Names( "String", Continuous ),
Num( col_name ) > 0
);
dtArray = dt14 << Stack(
invisible,
columns( Lookup ),
Source Label Column( "theKeys" ),
Stacked Data Column( "valeur ST1-2" ),
Drop All Other Columns( 1 ),
Output Table( "Untitled 45.jmp" )
);
dtArray << delete rows( dtArray << get rows where( Is Missing( :"valeur ST1-2"n ) ) );
theLookup = Associative Array(
dtArray:"valeur ST1-2"n << get values,
dtArray:theKeys << get values
);
Close( dtArray, nosave );
// Loop through the rows and assign the new values
For Each Row(
row = Row();
If( :"valeur ST1-2"n != 0,
lookup = theLookup[:"valeur ST1-2"n];
If( :"valeur ST1-2"n == As Column( lookup ),
As Column( "Durée " || theLookup[:"valeur ST1-2"n] ) =
Num(
As Column( "Date fin " || theLookup[:"valeur ST1-2"n] )
-As Column( "Date début " || theLookup[:"valeur ST1-2"n] )
);
Continue();
);
,
Continue()
);
);
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: date
Thank you for your reply !
This seems to work for the first two lines, but when the ST1-2 value value changes there is an error.
We notice that for the 4th line, the duration 2231 is zero while there should be the difference end date 2231 - start date 2231 (because on the 4th line the value of ST1-2 value is 82 and 82 corresponds to the object 2231).
Here is the error message I get:
Perhaps in the table "TheLookup" there is an inconsistency, because objects can be assigned to different OPs.
For example below: we see that for "theKeys" = 2041 there is the value "ST1-2 value" is equal to "60" and "47"
This post originally written in French and has been translated for your convenience. When you reply, it will also be translated back to French .
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: date
To determine the keys, all of the columns named 2041, 711, 2231 are brought together and an Associative Array is created that when number 60 is found in column valeur ST1-2 it will lookup in the Associative Array and it will return 2241. If a 47 was entered, it would also return a 2241. If an 82 is found, a 2231 is returned. I made an assumption that the numbers found in the columns called 2041, 711, 2231, etc. have the various numbers that could be found in valeur ST1-2.
If you could attach the sample data table that you are using, I will try to figure out the issue you are seeing.