Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

nicolas_sinquin
New Contributor III

Count where date1 < date from 2 tables

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.

6 Replies
YoussefBelloum
Esteemed Contributor

Re: Count where date1 < date from 2 tables

Hi,

how you're calculating the Nb_ACCOUNT measure ?

ogautier62
Valued Contributor II

Re: Count where date1 < date from 2 tables

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

MVP
MVP

Re: Count where date1 < date from 2 tables

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.

nicolas_sinquin
New Contributor III

Re: Count where date1 < date from 2 tables

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
Valued Contributor II

Re: Count where date1 < date from 2 tables

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
Esteemed Contributor

Re: Count where date1 < date from 2 tables

Yes, after reading the answers, I understood your request