Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello. There are two tables:
I want to calculate a number of customers who did any operation during the session, and who is not. And if a customer did some operations then to calculate how many operation he did and a time of the first one.
This is an example of how it can be done in the load script, using INTERVALMATCH as suggested by Marco.
See the script for an explanation.
Best,
Peter
Hi,
you could transform table A using generic load in order to get start and end time in one row (one row per Session/customer id). Using intervalmatch you could then create a link table.
please provide sample data to demonstrate.
hope this helps
regards
Marco
This is an example of how it can be done in the load script, using INTERVALMATCH as suggested by Marco.
See the script for an explanation.
Best,
Peter
Thanks a lot! It is exactly what I need.
But I faced with one problem.
In my case i have two more fields in SessionData table.
So SD2 table looks like: CustomerID, SessionStart, SessionStop, setting1,setting2
And I need this two fields in my Operations table.
The only way i found is to use left join again:
LEFT JOIN(Operations)
LOAD SessionStart, SessionStop, CustomerID, setting1,setting2
RESIDENT SD2;
DROP Table SD2;
I have sinking feeling that it is not a best practice.
Don't worry. If it works, it works. Your solution is allright.
You can also select the not-so-clean datamodel with a synthetic key.
Add your settings columns to the SessionData table. Drop the LEFT JOIN prefix from the INTERVALMATCH LOAD. And finally, delete the DROP Table SD2 statement.
After reloading, you'll get a datamodel with a third table (the intervalmatch table) and a synthetic key that keeps Sessions and Operations separate. Although it doesn't look nice, this model is perfectly ok and is an example of making good use of synthetic keys.
See Henric's explanation of the synthetic key advantages in IntervalMatch