I've a table with some connexion, and an other with the account created date.
LOAD * INLINE [
LOAD * INLINE [
I would like to have, for each connexion, the number of account created on the date
result wanted :
ID_PER, DATE_CONNEXION Nb_ACCOUNT
1 07/03/2018 2
1 09/03/2018 3
1 10/03/201 3
2 08/03/2018 3
2 09/03/2018 3
3 04/03/2018 1
3 05/03/2018 1
3 06/03/2018 2
I tried with a cartesian cross, but it give me 800 000 000 lines ... a little bit to much.
Thanks for your help.
load count(id_per),date_creation resident creation;
so you have total account created per day
then join your master calendar with date_creation :
you get fields :
load this to create a new field nb_account as
nb_account = peek('nb_account') + nb_creation_account to get total account at this date
for the result you load connexion link to calendar with date_connexion and you retreive nb_created_account
The other option is to keep them as two separate tables and then the Cartesian will take place on the front end expression. The good is that if you look at this after making selections, you will not see an extremely bad performance. The bad is that it will still be slow.
It's the account number on the date connexion.
In the end, i want a graph showing total connexion and total account , per day, week, month (selection by qsvariable), but also keeping other selection (city, age ....)
@ Olivier : i'll try and come back.
load count(id_per),date_creation resident creation group by date-creation;
as Sunny said, it's not necessary to do final load (link in front end better)