cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
saitcopuroglu
Level IV

Delaying (lag) a row value by column date

Dear All,

I would like to make a simple calculation but I can not.

Attached jmp table is a subset of Hotel Occupancy Data Base.

Simple logic for Record ID 27421 is;

in 14 Apr 2016 250 Rooms arrive and check in,

in 14-15-16 Apr 2016 those 250 Rooms stay at the hotel for 3 days long,

in 17.04.2015 the 250 Rooms which arrived in 14 Apr 2016 Check Out (Leave the hotel).

I need to calculate the RoomLeave Column (which I filled manually for the purpose)..

Record ID is unique but many more arrivals/stay/leave occur at the same dates with different Record IDs.

Many thanks in advance for any of your support.

11173_Screen Shot 2016-03-24 at 23.06.33.png

1 ACCEPTED SOLUTION

Accepted Solutions
txnelson
Super User

Re: Delaying (lag) a row value by column date

Are the data for each RecordID always grouped together and in ascending order based upon StayDate?

If they are, here is a formula that will work:

If( :RecordId != Lag( :RecordId ) | Row() == 1,

ar = :ArrivalRoom

);

If( Date Difference( :StayDate, :CheckOutDate, "Day" ) == 1,

ar,

0

);

Jim

View solution in original post

7 REPLIES 7
KarenC
Super User (Alumni)

Re: Delaying (lag) a row value by column date

Would the date difference function work for you?

If Date Difference(CheckOutDate, CheckInDate, Day) = 1 then room leave = room night, else 0.

saitcopuroglu
Level IV

Re: Delaying (lag) a row value by column date

Dear karen@boulderstats​ thank you for the input. Either I did it wrong or the formula itself gives error.

11182_Screen Shot 2016-03-25 at 10.16.31.png

KarenC
Super User (Alumni)

Re: Delaying (lag) a row value by column date

Yes, I didn't provide the exact correct formula.  You got close.  It is the "Day" piece that is tricky. Jim used the same formula in the script he wrote only he has it correct in that you need the quotes around "Day".  With many JMP formula's the options can be tricky (they generally make sense once you see them) so I find I often need the user manual to get all of the pieces correct.

txnelson
Super User

Re: Delaying (lag) a row value by column date

Here is a quick script that performs the data table updating you want.

 

Names default to here(1); 

dt=current data table();

   

// Create the RoomLeave column and initialize it 

If(Try(dt:RoomLeave<<get name,"")!="", dt<<delete columns("RoomLeave")); 

wait(.2); 

dt<< New Column("RoomLeave", set each value(0));

   

// Find the last staydate and the Arrival Room for each RecordID 

dtsumm = dt <<  Summary(invisible, 

       Group( :RecordId ), 

       Max( :StayDate ), 

       Max( :ArrivalRoom ), 

       Freq( "None" ), 

       Weight( "None" ), 

       statistics column name format( "column" ) 

);

   

// Change the ArrivalRoom to the RoomLeave 

dtsumm:ArrivalRoom << Set Name("RoomLeave"); 

// Cleanup the data table to keep only the columns needed 

dtsumm << delete columns("N Rows");

   

// Update the original data table with the Leaving data 

// Matching on the RecordID and the last staydate 

dt <<  Update( 

       With(dtsumm ), 

       Match Columns( :RecordId = :RecordId, :StayDate = :StayDate ) 

);

// Delete the summary data table 

close( dtsumm, nosave );

 

Jim
saitcopuroglu
Level IV

Re: Delaying (lag) a row value by column date

Dear Jim Nelson,
Thank you for the input it does the job but I need to solve it with a formula column, the data base is huge and updating itself in every minute.

txnelson
Super User

Re: Delaying (lag) a row value by column date

Are the data for each RecordID always grouped together and in ascending order based upon StayDate?

If they are, here is a formula that will work:

If( :RecordId != Lag( :RecordId ) | Row() == 1,

ar = :ArrivalRoom

);

If( Date Difference( :StayDate, :CheckOutDate, "Day" ) == 1,

ar,

0

);

Jim
saitcopuroglu
Level IV

Re: Delaying (lag) a row value by column date

Thank you Jim Nelson! It works and yes the data is in ascending order.

Many thanks again, I can pull my report.

11186_Screen Shot 2016-03-25 at 18.11.16.png

P.S.: Unfortunately the 'day' have to come from the StayDate for the ArrivalRoom and RoomNight but does not fit for RoomCout which needs a +1 day delay (A guest can not stay and Check Out same day). Acc. to my logic it is impossible to solve it in one tabulate report like the one above...