topic Time interval between two tables in App Development

Time interval between two tables

Talso — Sun, 19 Dec 2021 10:08:15 GMT

Hi,

I have two tables: events table and campaign table.

I want to calculate the time interval between the rows on the tables in order to measure the effectiveness of the campaigns.

I want to do that on the script so that it wouldn't have an effect on the performance.

Table examples:
1. Events that a user did

date and time	event name	user email
12/15/2021 8:19:20 AM	registered	a@a.com
12/15/2021 8:20:20 AM	login	a@a.com
12/19/2021 8:17:20 AM	paid	b@b.com

2. Campaigns information

date and time	campaign name	campaign event	user email
12/13/2021 2:10:20 AM	aaa	sent	a@a.com
12/15/2021 8:16:20 AM	aaa	view	a@a.com
12/15/2021 8:17:20 AM	aaa	click	a@a.com
12/17/2021 2:10:20 AM	bbb	sent	a@a.com
12/17/2021 2:10:20 AM	bbb	sent	b@b.com

I don't want it to be on the same table because it will multiply the data.

The goal is that the user will be able to change the time period on the UI and see the percentage of 'login' users (for example) after he opens/views the campaign.

Thanks,
Tal.

Re: Time interval between two tables

MarcoWedel — Sun, 19 Dec 2021 13:17:36 GMT

given your sample data, what would be your expected result?

Re: Time interval between two tables

Talso — Sun, 19 Dec 2021 14:52:47 GMT

Hi,

I want to be able to create a new field that will calculate the time difference between the events in table 1 and events in table two.

if those fields were on the same table, I would do this calc:

Interval( (event_date_time) - (campaign_date_time), 'hh:mm') as hour_difference

Thanks!

Re: Time interval between two tables

MarcoWedel — Sun, 19 Dec 2021 20:23:12 GMT

could you please post your sample data including this new field to demonstrate where it should be created and which values you would expect to see?

Re: Time interval between two tables

kef — Mon, 20 Dec 2021 08:46:37 GMT

Hi,

You can use the ApplyMap function but i don't understand how join event information and campaign information (only with user email ?).