day difference in the same column of different scenarios

[#1]

Hi, I have questions for different scenarios as below. Really appreciate any help!

Scenario A: I want to add this last column, calculating for that userID, what's the day difference from first web to first booking date. If he had no web action prior to booking action, then leave it empty. Could someone help me with what the formula should look like using the function but not jsl?

userID	action	date	first web date to first booking date
2523	web	2018-05-06	22
2523	web	2018-05-16	22
2523	booking	2018-05-28	22
2523	booking	2018-06-08	22
2523	web	2018-07-08	22
2523	booking	2018-08-08	22

Scenario B: based on the above table, I want to calculate another table like the below that contains for each userID, day difference between 'the first web action date' 'before bookings' to each booking date. Based on the logic, the ideal result with the above table as input will be as below, 3 instances of day difference for this userID. Same as in scenario A, we only consider when web is prior to booking.

userID	instance	calculation
2523	22	5/28 minus 5/6
2523	33	6/8 minus 5/6
2523	31	8/8 minus 7/8

Thank you!

[#2]

Hi Joann,
Couple of questions to see if a simple solution could be developed:
1) Would you happen to have a column that would differentiate between the first sequence (rows 1 - 4) and the second sequence (rows 4 - 6); without this, it is a bit more challenging to create column formulas that would distinguishing the 2 set of transactions.
2) If I understand correctly, the second booking date (row 4) is not relevant to your question.
Let us know at your earliest convenience.
Best,
TS

[#3]

Hi Joann,

After playing around a bit, I was able to produce a set of column formulas that produce the desired output (see attached). Importantly, the table has to be sorted by "userId" and "date" for the formulas to work properly.

Best,

TS

[#4]

Thank you Thierry! The result looks good! Could you please explain to me what's Transaction ID doing? I don't understand the logic/formula.
Do you happen to have the solution for scenario B? In scenario B the second bookings matters.
Thank you!

[#5]

I think the following formula will do for scenario A:

It is not yet optimized, but may show you how things like this can be done.
It uses some simple jsl in a formula, step by step.

Georg_0-1617534078891.png

Please see full script for table generation:

New Table( "20210404_user_web_booking_matrixexample",
	Add Rows( 12 ),
	Compress File When Saved( 1 ),
	New Column( "userID",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values(
			[2523, 2523, 2523, 2523, 2523, 2523, 2540, 2540, 2540, 2540, 2540, 2540]
		),
		Set Display Width( 66 )
	),
	New Column( "action",
		Character( 16 ),
		"Nominal",
		Set Values(
			{"web", "web", "booking", "booking", "web", "booking", "web", "web",
			"booking", "booking", "web", "booking"}
		),
		Set Display Width( 73 )
	),
	New Column( "date",
		Numeric,
		"Continuous",
		Format( "y/m/d", 12 ),
		Input Format( "y/m/d" ),
		Set Values(
			[3608409600, 3609273600, 3610310400, 3611260800, 3613852800, 3616531200,
			3647462400, 3647548800, 3647635200, 3647721600, 3647808000, 3647894400]
		),
		Set Display Width( 92 )
	),
	New Column( "first web date to first booking date",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [22, 22, 22, 22, 22, 22, ., ., ., ., ., .] ),
		Set Display Width( 150 )
	),
	New Column( "formula",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Formula(
			Names Default To Here( 1 );
			dat_vec = :date << get values;
			web_vec = :action << get values;
			user_vec = :userID << get values;
			user_index = user_vec == :userID[Row()];
			web_index = Matrix(
				Substitute( :action << get values, "web", 1, "booking", 0 )
			);
			web_dat = dat_vec[Loc( user_index & web_index )];
			booking_dat = dat_vec[Loc( user_index & !web_index )];
			(Minimum( booking_dat ) - Minimum( web_dat )) / (3600 * 24);
		),
		Set Selected
	)
)

[#6]

Thank you Georg! It works. Could you explain to me the logic behind this script? I'm not that familiar with Matrix and Loc here.
Do you happen to have the solution for scenario 2?
Thank you!

[#7 · Accepted solution]

Hi @joann , sorry to say but this is not the platform for full service, but we are happy if we can support you. So if you want to learn please see the online documentation on the functions used here.

E.g. loc gives you the index of a vector for certain values:

Loc Functions (jmp.com)

Please see below script for both scenarios. Scenario B is quite similar ...

If you want to have the exact table for scenario B as you described, you can delete the formula (that keeps the values then), and select the missing values in column "Formula B" and delete them.

BR 

names default to here(1);

// create the example table
New Table( "20210404_user_web_booking_matrixexample",
	Add Rows( 12 ),
	Compress File When Saved( 1 ),
	New Column( "userID",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values(
			[2523, 2523, 2523, 2523, 2523, 2523, 2540, 2540, 2540, 2540, 2540, 2540]
		),
		Set Display Width( 66 )
	),
	New Column( "action",
		Character( 16 ),
		"Nominal",
		Set Values(
			{"web", "web", "booking", "booking", "web", "booking", "web", "web",
			"booking", "booking", "web", "booking"}
		),
		Set Display Width( 73 )
	),
	New Column( "date",
		Numeric,
		"Continuous",
		Format( "y/m/d", 12 ),
		Input Format( "y/m/d" ),
		Set Values(
			[3608409600, 3609273600, 3610310400, 3611260800, 3613852800, 3616531200,
			3647462400, 3647548800, 3647635200, 3647721600, 3647808000, 3647894400]
		),
		Set Display Width( 92 )
	),
	New Column( "first web date to first booking date",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [22, 22, 22, 22, 22, 22, ., ., ., ., ., .] ),
		Set Display Width( 150 )
	),
	New Column( "formula_A",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Formula(
			Names Default To Here( 1 );
			// convert columns to vectors
			dat_vec = :date << get values;
			web_vec = :action << get values;
			user_vec = :userID << get values;
			// get a vector with subscripts of current userID
			user_index = user_vec == :userID[Row()];
			// get a vctor with subscripts where action=web
			web_index = Matrix(
				Substitute( :action << get values, "web", 1, "booking", 0 )
			);
			// get a vector with dates for current userID and action=web
			web_dat = dat_vec[Loc( user_index & web_index )];
			// the same for booking (actually not web)
			booking_dat = dat_vec[Loc( user_index & !web_index )];
			// calculate minimum of both date vectors and subtract
			(Minimum( booking_dat ) - Minimum( web_dat )) / (3600 * 24);
		),
		Set Display Width( 146 )
	),
	New Column( "formula_B",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Formula(
			Names Default To Here( 1 );
			dat_vec = :date << get values;
			web_vec = :action << get values;
			user_vec = :userID << get values;
			user_index = user_vec == :userID[Row()];
			web_index = Matrix(
				Substitute( :action << get values, "web", 1, "booking", 0 )
			);
			web_dat = dat_vec[Loc( user_index & web_index )];
			booking_dat = dat_vec[Loc( user_index & !web_index )];
			// when current action is booking, then subtract minimum web date, otherwise keep empty, and divide seconds by seconds of day
			If( :action == "booking", :date - Minimum( web_dat ), . ) / (3600 * 24);
		),
		Set Selected
	)
);

[#8]

It works! Thank you very much Georg! Will learn from the link of Matrix and Loc. Really appreciate your help.