Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
Not applicable

How to count distinct value depending on some other tables/values

Hi everyone,

I have been searchin and trying for a long time but I'am stuck with this problem .

I really need help.

I am sure there is an easy QlikView solution..

I have 2 tables: Transport and vehicules.

In my table vehicule, only some of them are available for transport.

Transport occurs to a date.

Vehicules can make more than one transport the same day.

I need to count the number of different vehicules that have been working per day and those that haven't benn working that same day in comparison to my number of available vehicules .

Exemple of data

Tables:        TRANSPORT                       VEHICULE

                    Date           Vehicule            Vehicule   Available?

                   01/01/2012  A                       A             Y   

                    01/01/2012  A                       B             Y

                    01/01/2012  A                       C             Y

                   01/01/2012  B                       D             Y

                   01/01/2012  C                       E             N

                   02/01/2012  A

                   02/01/2012  B

                   02/01/2012  B

                   02/01/2012  A

                   03/01/2012  C

                   03/01/2012  C

                   03/01/2012  C

So here, I have a total of 4 vehicule available.

on the 01/01/2012, 3 out of 4 were actually active and 1 out of 4 was inactive.

on the 02/01/2012, 2 out of 4 were actually active and 2 out of 4 was inactive.

on the 03/01/2012, 1 out of 4 were actually active and 3 out of 4 was inactive.

Seems really to be easy but I can't get trhough even with set analysis..

Thanks for your help !!!

caroline .

1 Solution

Accepted Solutions

Re: How to count distinct value depending on some other tables/values

Check the attached qv file.

Hope it helps

Celambarasan

View solution in original post

6 Replies

Re: How to count distinct value depending on some other tables/values

Is Count(Distinct Vehicule) not working?

Not applicable

Re: How to count distinct value depending on some other tables/values

No.

I tried it 😉

If I select a day, of course I can easily get the distinct count of the ones that have been driving .

But then I can't get the inactive and the correct total.

I still need to be able to select a date.

so, for day 01/01/2012 i can have:

=Count({<Available?={"Y"}>} Distinct Vehicule) => give me 3 OK

=Count({1<Available?={"Y"}>} Distinct Vehicule) => will give me the 5 number instead of 4

Re: How to count distinct value depending on some other tables/values

Check the attached qv file.

Hope it helps

Celambarasan

View solution in original post

Not applicable

Re: How to count distinct value depending on some other tables/values

Thanks !

Works perfectly !

How I didn't see that 🙂  !

Not applicable

Re: How to count distinct value depending on some other tables/values

Hi,

Now my data get more complicated 😉

I have a type of vehicule and 2 dates that tells me when the vehicule was bought and when it was sold.

How can I get the inactives for each type of vehicule and take into account the selling part?

In other words, if my vehicule was sold at the selected date, it shouldn't be counted as actif (of course) neither inactif as it wasn't part of my fleet?

See the qvw in attached.

For the 3 of january I should have a total vehicule = 3 and only one active.

Thnaks for your help 😉

Not applicable

Re: How to count distinct value depending on some other tables/values

Please Does somebady have an idea for me?

I am thinking about maybe some adjustement in my data model..

Thanks!