- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
row operations
For(x=1,x<=N Rows(dt),x++,
If(IsMissing(Column( dt, "Actual Finish Date_" )[x]),
Column( dt, "bouge pas" )[x] = Column( dt, "Operation Due Date_" )[x] - :Operation Start Date_ [x-1],
Column( dt, "bouge pas" )[x] = Column( dt, "Actual Finish Date_" )[x] - :Actual Execution Start Date_ [x]
);
);
Hello, my problem is that I want in the first line of the for, the line x-1 for the column :Operation Start Date_ but this syntax does not work ? Thanks
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: row operations
If row 1's value for Actual Finish Date is missing, none of the currently proposed scripts will work. Therefore, that issue needs to be handled. The below code, a simple modification to the original JSL will work.
Note: I made a logic error on the original version of the code. I have changed it below.
For( x = 1, x <= N Rows( dt ), x++,
If( Is Missing( Column( dt, "Actual Finish Date_" )[x] ),
If( Row() > 1,
Column( dt, "bouge pas" )[x] = Column( dt, "Operation Due Date_" )[x] - :Operation Start Date_[x - 1],
Column( dt, "bouge pas" )[x] = 0; // Set the value if there is not an X-1 row (i.e. row 1)
),
Column( dt, "bouge pas" )[x] = Column( dt, "Actual Finish Date_" )[x] - :Actual Execution Start Date_[x]
);
);
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: row operations
If the first row has an empty Actual Finish Date_ then your script looks for the previous row, which will not work. You need to replace this line with something that doesn't reference the previous row:
:"bouge pas"n = :"Operation Due Date_"n - :Operation Start Date_[Row() - 1],
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: row operations
if you are looping over a data table's rows (usually I would try to avoid it and use formula or set each value) I would suggest using For Each Row() instead of For(). Without having example data I would guess that the issue could be that you will get 0 as row number, which isn't valid in JMP (Invalid Row Number at row 2 in access or evaluation of ':Operation Start Date_[ /*###*/x - 1]' , :Operation Start Date_[/*###*/x - 1])
Try setting x values manually and seeing what you get, add debug prints and take a look at log
Here is example script with couple of optional ideas on how you could set bouge pas columns values
Names Default To Here(1);
dt = New Table("Untitled",
Add Rows(3),
New Column("Actual Finish Date_", Numeric, "Continuous", Format("Best", 12), Set Values([1, 4, 6])),
New Column("Actual Execution Start Date_", Numeric, "Continuous", Format("Best", 12), Set Values([0, 2, 3])),
New Column("Operation Start Date_", Numeric, "Continuous", Format("Best", 12), Set Values([10, 21, 32])),
New Column("Operation Due Date_", Numeric, "Continuous", Format("Best", 12), Set Values([11, 22, 33])),
New Column("bouge pas", Numeric, "Continuous")
);
For(x = 1, x <= N Rows(dt), x++,
If(Is Missing(Column(dt, "Actual Finish Date_")[x]),
Column(dt, "bouge pas")[x] = Column(dt, "Operation Due Date_")[x] - :Operation Start Date_[x - 1],
Column(dt, "bouge pas")[x] = Column(dt, "Actual Finish Date_")[x] - :Actual Execution Start Date_[x]
);
);
/* better option in my opinion
For Each Row(dt,
If(Is Missing(:"Actual Finish Date_"n),
:"bouge pas"n = :"Operation Due Date_"n - :Operation Start Date_[Row() - 1],
"bouge pas"n = :"Actual Finish Date_"n - :Actual Execution Start Date_
);
);
*/
/* usually best option
Column(dt, "bouge pas") << Set Each Value(
If(Is Missing(:"Actual Finish Date_"n),
:"Operation Due Date_"n - :Operation Start Date_[Row() - 1],
:"Actual Finish Date_"n - :Actual Execution Start Date_
);
);
*/
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: row operations
If row 1's value for Actual Finish Date is missing, none of the currently proposed scripts will work. Therefore, that issue needs to be handled. The below code, a simple modification to the original JSL will work.
Note: I made a logic error on the original version of the code. I have changed it below.
For( x = 1, x <= N Rows( dt ), x++,
If( Is Missing( Column( dt, "Actual Finish Date_" )[x] ),
If( Row() > 1,
Column( dt, "bouge pas" )[x] = Column( dt, "Operation Due Date_" )[x] - :Operation Start Date_[x - 1],
Column( dt, "bouge pas" )[x] = 0; // Set the value if there is not an X-1 row (i.e. row 1)
),
Column( dt, "bouge pas" )[x] = Column( dt, "Actual Finish Date_" )[x] - :Actual Execution Start Date_[x]
);
);
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: row operations
For example for lines 2, 3 4... the result is 6 days when it should be 1 day
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: row operations
For Each Row(dt,
If(Is Missing(:"Actual Finish Date_"n),
:"bouge pas"n = :"Operation Due Date_"n - :Operation Start Date_[Row() - 1],
"bouge pas"n = :"Actual Finish Date_"n - :Actual Execution Start Date_
);
)
It still doesn't work because from the first line I have empty lines in the column :Actual Finish Date_ and I don't know how to do it
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: row operations
If the first row has an empty Actual Finish Date_ then your script looks for the previous row, which will not work. You need to replace this line with something that doesn't reference the previous row:
:"bouge pas"n = :"Operation Due Date_"n - :Operation Start Date_[Row() - 1],