How to find distinct data points per person

[Anonymous]

Hi there,

I am looking for a way to list distinct data points on a user and then show it in groups after.

Example is a I have an email, name, zip code, and birthday for Bob.  That would be 4 data points.  We only have Susan's Name and Birthday so that's only 2 data points.

Then I could also do a sum of people with 2 data points and so on.

The data would be null in the fields if there isn't any collected yet for that person.

A table simplified like this for example:

user	name	email	zipcode	birthday
BobD	Bob Kelly	Bob@Kelly.com	90210	12/12/2001
Susan O	Susan Oleade	Susan@O.com
DaveC	Dave Clarke		10001

[sunny_talwar · Accepted solution]

Try like this.

Table:

LOAD user,

    name,

    email,

    zipcode,

    birthday,

    RangeCount(If(Len(Trim(name)) > 0, name),

      If(Len(Trim(email)) > 0, email),

      If(Len(Trim(zipcode)) > 0, zipcode),

      If(Len(Trim(birthday)) > 0, birthday)) as DataPointCount

FROM

[https://community.qlik.com/thread/216119]

(html, codepage is 1252, embedded labels, table is @1);

I had to use Len(Trim()) to check for white space, but in your case, if the values are truly null, then you might just be able to use this:

RangeCount(name, email, zipcode, birthday) as DataPointCount

[Anonymous]

That's great.  Exactly what I was looking for.

Another work around I just got to as well is making an expression in a straight table for:

count(DISTINCT birthdate) + if(trim(zipcode='',0, count(DISTINCT trim(zipcode))) + if(trim(last_name)='', 0, count(DISTINCT last_name))

I will try both and see which one gives a cleaner answer for charting.

I am thinking the  one with the Len(Trim) will.

I greatly appreciate your assistance.

[sunny_talwar]

It seems you need this information on the front end of the application. If that's true, I wouldn't even recommend using Len(Trim())... that part should be fixed during the application reload. May be something like this:

Table:

LOAD user,

    If(Len(Trim(name)) > 0, name) as name,

    If(Len(Trim(email)) > 0, email) as email,

    If(Len(Trim(zipcode)) > 0, zipcode) as zipcode,

    If(Len(Trim(birthday)) > 0, birthday) as birthday

FROM

[https://community.qlik.com/thread/216119]

(html, codepage is 1252, embedded labels, table is @1);

Now once you have fixed the white space and made it into null, you can simply use RangeCount() function

[Anonymous]

i think sunny give the correct answer

RangeCount(If(Len(Trim(name)) > 0, name),

      If(Len(Trim(email)) > 0, email),

      If(Len(Trim(zipcode)) > 0, zipcode),

      If(Len(Trim(birthday)) > 0, birthday)) as DataPointCount

[Anonymous]

Thanks.  I have never used rangecount.  To finish this off, what is the best way to also display the groups of data points?  Like 650 groups of data points with 3, and 234 groups of data points of 5 and so on.  Assuming you have a lots of like a thousand or more.

This bar chart could  show a user where to concentrate more energy on in collecting more data.

[Anonymous]

Ok I got it.  I just used the rangecount formula as a dimension.

Thanks again

[sunny_talwar]

So it seems that you are not trying to count the number of data point available within name, email, zipcode, birthday. Is this true?

[Anonymous]

Well yes I want to do a few things here.  One is to show show a graph on the data points and the number each has.  It would be good at a glance to see we have 650 zipcodes and 1100 email addresses.  I am thinking a bar chart with the different data points at the bottom.  I am also using this in a straight table when you can sort by data point number and export a user list that way too. 

Right now the bar chart seems to be the more difficult one.

[sunny_talwar]

See if this helps