Count where date1 < date from 2 tables

[Anonymous]

Hye,

I've a table with some connexion, and  an other with the account created date.

CONNEXION:

LOAD * INLINE [

ID_PER, DATE_CONNEXION

1 ,07/03/2018

1,09/03/2018

1,10/03/201

2,08/03/2018

2,09/03/2018

3,04/03/2018

3,05/03/2018

3,06/03/2018

];

CREATION:

LOAD * INLINE [

ID_PER, DATE_CREATION

1,06/03/2018

2,08/03/2018

3,04/03/2018

];

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.

[YoussefBelloum]

Hi,

how you're calculating the Nb_ACCOUNT measure ?

[ogautier62]

Hi,

CreationAccount:

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 :

date,nb_creation_account

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

regards

[sunny_talwar]

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.

[Anonymous]

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.

[ogautier62]

correction :

CreationAccount:

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)

[YoussefBelloum]

Yes, after reading the answers, I understood your request