topic Re: How do I place values into data table cells using JSL in Discussions

How do I place values into data table cells using JSL

mdawson69 — Wed, 26 Aug 2015 13:40:19 GMT

I am relatively new to JSL, but I have been authoring several scripts to handle processes for generating tables and graphs for testing that a software product that we are producing is properly reporting results. One of the issues I am having now is that I have to create a graph that shows reported times (easy) but also reflects the residuals from the reporting time bin into future time bins.

For example, I need to make a wait time graph where the x-axis runs from midnight to midnight with discrete 15-minute time bins. I have a data set that reports wait times and I bin the time that the wait begins. For simplicity, I single out one row where a wait time of 90 minutes is reported for an agent that enters a location at 9:05 AM. The time bin for that wait time is 9:00 AM the 90 minute wait time is reported and shown in the graph in the 9:00 AM time bin. In a traditional graph, all other times would be zero, as all other data was excluded, but in reality, the graph should show what that agent's wait time is across several time bins until the wait time is zero; effectively, what is the minimum amount of time the next person entering the location can expect to wait if they arrive at the location behind the agent waiting during any time bin where the current agent is still queued.

So I need to create a matrix of all times with values of zero in the time bins except for those listed below.

Time Bin Wait Time

9:00 AM 90 min

9:15 AM 80 min

9:30 AM 65 min

9:45 AM 50 min

10:00 AM 35 min

10:15 AM 20 min

10:30 AM 5 min

I already have a script that generates the columns for each time bin after the last column of the data table, and I have found that due to the number of conditions involved and that fact that where, when and how often nonzero wait times will occur in any given row of the matrix that using column formulas would be overcomplicated, if at all feasible. So I was hoping that there is some method for populating individual cells so that I use nested loops to go through each row of each column in the matrix and determine what wait time value should be placed therein.

My other option, which I would much prefer, would be to create a new data table that includes child records to handle the overflow data. For example, a subset of the data table is the following four rows:

Agent ID	Location	Day	Entry Time	Exit Time	Wait Time	Time Bin
184	Egress Queue	1	7:10:24 AM	7:10:24 AM	0	7:00 AM
185	FIS Entrance	1	6:41:38 AM	6:41:38 AM	0	6:30 AM
185	Visitors Queue	1	6:41:38 AM	7:00:51 AM	1153	6:30 AM
185	Baggage Claim	1	7:01:59 AM	7:01:59 AM	0	7:00 AM

The script would scan each row in the original table and copy it to the new data table. In the third row in the example above, the total wait time, 1153 s, is greater than the time spent waiting in the 6:30 AM time bin: 6:45 AM time bin - 6:41:38 AM queue entry time - 0 s queue transit time = 202 s of wait used in 6:30 AM time bin. So the new data table would add child rows resulting in the region of the data table shown above appearing as follows:

Agent ID	Location	Day	Entry Time	Exit Time	Wait Time	Time Bin
184	Egress Queue	1	7:10:24 AM	7:10:24 AM	0	7:00 AM
185	FIS Entrance	1	6:41:38 AM	6:41:38 AM	0	6:30 AM
185	Visitors Queue	1	6:41:38 AM	7:00:51 AM	1153	6:30 AM
185	Visitors Queue	1	6:41:38 AM	7:00:51 AM	951	6:45 AM
185	Visitors Queue	1	6:41:38 AM	7:00:51 AM	51	7:00 AM
185	Baggage Claim	1	7:01:59 AM	7:01:59 AM	0	7:00 AM

The rows in italics are the child rows for Agent 185 in the Visitors Queue showing the remaining wait times until Agent 185 has no more wait in the Visitors Queue. I could then use this table to generate the graph that I need because the child records would provide the additional data needed.

If anyone can provide a method for either of this solutions, it would be greatly appreciated.

Re: How do I place values into data table cells using JSL

ron_horne — Fri, 28 Oct 2016 13:20:28 GMT

Hi md,

i am not sure i fully understand your question but try these two links:

1) Expand by Frequency Column Add-In

2)bubble plots - unequal time intervals

Re: How do I place values into data table cells using JSL

mdawson69 — Fri, 28 Aug 2015 19:05:13 GMT

Ron,

Thanks for the info. The Add-in did not work for me, but reviewing your code for the bubble plot problem resolved the issue I was having. It took all day to get the script I needed built, but the code you provided for adding child rows to fill in data gaps helped me complete what would have otherwise remained a dead end for quite some time.

Re: How do I place values into data table cells using JSL

mdawson69 — Mon, 31 Aug 2015 10:18:18 GMT

Question.

In the code block where you create new rows based on the event duration,

For( i = 1, i <= rowN, i++,

rowi = dt << get row( i );

For( z = 1, z <= (:Name( "Event Duration" )[i] - 1), z++,

dt << add row( rowi );

dt << run formulas;

);

what does the line that is just z; do?

Re: How do I place values into data table cells using JSL

ron_horne — Wed, 02 Sep 2015 09:12:29 GMT

hi MD,

i am happy my script was of use to you. when i wrote it the add-in was not available.

regarding the z; i just checked it now and it is not necessary. the loop should work fine without it. I used to think you have to have the z in the for loop in order for it to count the times it goes through.

when testing the script it helped me control its action by having the number of the loop available all the time.

Best,

Ron.

Re: How do I place values into data table cells using JSL

mdawson69 — Wed, 02 Sep 2015 10:59:56 GMT

Thanks Ron.

That is good to know. That lines seemed to be extraneous, but as JSL is a scripting language and I have just started authoring my own scripts—I have actually been using JMP's scripting capabilities for a few years, but it was mostly saving scripts to a data table from a platform and performing a few tweaks when needed—I was not sure if or what it was doing in relation to adding the rows. At first, had a wild mad guess it may have had something to do with pre-populating the rows, I realized that the Add Row was doing that in an undocumented fashion.

Speaking of which, according to the JMP Scripting Guide, Add Rows(number) adds the specified number of rows, but the inner loop adds one new row at a time based on your event duration and copies the column values in the source row, contained as a list in rowi, into the new row. Kudos to you on figuring out this undocumented capability. This was one of the reasons I got stuck, as I have considerable programming experience and can often figure out a new language as long as the syntax is well documented. Nothing I have found in JMP's documentation mentions that the Add Rows function can accept a variable containing the list of column values for a specific row as a parameter.