topic Re: What's the best way to do this... in App Development

What's the best way to do this...

samvile18 — Thu, 25 Jul 2024 11:37:32 GMT

...I'm trying to find the best way to count the amount of logins from my Logins table where the date is between the campaign date and 30 days after it. My data layout looks like this:

It's joined by %Master_Id but I'm getting a bit stuck with getting the set analysis to work.

Any help would be greatly appreciated.

Re: What's the best way to do this...

rubenmarin — Thu, 25 Jul 2024 15:04:00 GMT

Hi, I think it will be better if you flag those records on script, you can do a join or a mapping the check each Login Date with the correspondig CampaignDate to create a binary field, then the expression could be as simple as:

Sum({<isIn30Days={1}>} [# of Logins])

Re: What's the best way to do this...

samvile18 — Fri, 26 Jul 2024 09:07:38 GMT

Hi Ruben,

Thanks very much for the reply, can you please elaborate a little more for me on the script solution?

I'm not too sure what you mean and I'm struggling to put it into practice.

I've attached a simple example script that joins my two tables as it currently is in the app, can you have a look and let me know how you would make the changes please?

Really appreciate your reply, thank you.

Re: What's the best way to do this...

rubenmarin — Fri, 26 Jul 2024 09:22:29 GMT

Hi, if you alrady have the join I think you only need:

Logins:

NoConcatenate

Load *,

If("Login Date"-CampaignDate<=30,1,0) as isLoginIn30Days

Resident LoginsTemp

order by %Master_Id, "Login Date" desc;

You can add simple table with [Login Date], CampaignDate as isLoginIn30Days fields to check the flag.

Re: What's the best way to do this...

samvile18 — Fri, 26 Jul 2024 10:09:51 GMT

I don't think I was clear enough - the tables aren't joined together, they're linked my %Master_Id...you mentioned a mapping, how would that work?

Here's what the table layout looks like:

Re: What's the best way to do this...

rubenmarin — Fri, 26 Jul 2024 10:19:03 GMT

Right, it was a left keep, somewhat I read left join.

The mapping table could be created as:

mapCampaignDate:

Mapping LOAD %Master_Id, CampaignDate

Resident CampaignContacts;

And then apply in the logins table as:

If("Login Date"-ApplyMap('mapCampaignDate',%Master_Id)<=30,1,0) as isLoginIn30Days

Re: What's the best way to do this...

samvile18 — Fri, 26 Jul 2024 13:53:15 GMT

This is incredibly close - thank you very much, I think I have some fine tuning to do in terms of max dates etc but this is definitely along the right lines.

Thanks for taking the time to have a look