topic Re: How to find distinct data points per person in QlikView

How to find distinct data points per person

Anonymous — Mon, 09 May 2016 17:05:56 GMT

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

Re: How to find distinct data points per person

sunny_talwar — Mon, 09 May 2016 21:03:14 GMT

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

Re: How to find distinct data points per person

Anonymous — Mon, 09 May 2016 21:22:23 GMT

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.

Re: How to find distinct data points per person

sunny_talwar — Tue, 10 May 2016 01:11:44 GMT

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

Re: How to find distinct data points per person

Anonymous — Tue, 10 May 2016 04:40:49 GMT

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

Re: How to find distinct data points per person

Anonymous — Tue, 10 May 2016 12:14:40 GMT

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.

Re: How to find distinct data points per person

Anonymous — Tue, 10 May 2016 17:15:30 GMT

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

Thanks again

Re: How to find distinct data points per person

sunny_talwar — Tue, 10 May 2016 17:29:11 GMT

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

Re: How to find distinct data points per person

Anonymous — Thu, 12 May 2016 14:40:54 GMT

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.

Re: How to find distinct data points per person

sunny_talwar — Thu, 12 May 2016 15:47:21 GMT

See if this helps

Re: How to find distinct data points per person

Anonymous — Thu, 12 May 2016 16:28:32 GMT

Ok I Imported data and gave a mockup bar chart on what I am trying to achieve.

I do like your Data Integrity chart. I will use that to see a high level of everything for this and other projects.

Thanks again!

Re: How to find distinct data points per person

sunny_talwar — Thu, 12 May 2016 16:56:24 GMT

Can you check the attached now

Re: How to find distinct data points per person

Anonymous — Thu, 12 May 2016 17:09:06 GMT

Ok that is it. Question. Using this will it show all fields? If I have 100 fields and only want to see the values of 6 of them. I haven't used $Field before. I am assuming that is what it is doing.

Re: How to find distinct data points per person

sunny_talwar — Thu, 12 May 2016 17:14:59 GMT

You can restrict fields from certain tables or by deciding what you wish to see. For instance I removed Email by using a calculated dimension:

=If(Not Match($Field, 'Email'), $Field)

Re: How to find distinct data points per person

sunny_talwar — Thu, 12 May 2016 17:16:46 GMT

For a more specific match, you can use:

If(Match($Field, 'FieldName1', 'FieldName2', 'FieldName3', 'FieldName4', 'FieldName5', 'FieldName6'), $Field)

and then Select 'Suppress When Value Is Null' on the dimensions tab

Re: How to find distinct data points per person

Anonymous — Thu, 12 May 2016 19:37:52 GMT

I tried this in a dashboard with other fields and got No Data to display. I changed the field names to match what I have.

The expression I used too was: Count(email) - $(=concat('if($Field=' & chr(39) & $Field & chr(39) & ',nullcount([' & $Field & '])', ',') & concat(right($Field&')',1)))

I changed the Count(email) to match my unique field.

Let me know if I should be changing anything. This is great stuff you are doing here.

Re: How to find distinct data points per person

sunny_talwar — Thu, 12 May 2016 21:01:02 GMT

You might be already doing it, but you did not talk about it and I want to make sure you are not missing this, but do you have $Field as the dimension or not?

Re: How to find distinct data points per person

rwunderlich — Thu, 12 May 2016 23:12:02 GMT

Sunny,

I think you could simplify the script a bit as:

-rangesum(Len(Trim(name)) > 0,

Len(Trim(email)) > 0,

Len(Trim(zipcode)) > 0,

Len(Trim(birthday)) > 0

) as DataPointCount

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Re: How to find distinct data points per person

Anonymous — Fri, 13 May 2016 14:49:38 GMT

Yes I am using the $Field as a dimension.

So I tried it with a new data set from an excel file and it works fine.

But my data set shows no data. When I remove $(=concat('if($Field=' & chr(39) & $Field & chr(39) & ',nullcount([' & $Field & '])', ',') & concat(right($Field&')',1))) from the expressions and just leave Count(Email) it shows a total overall same number for all fields. So for some reason that formula doesn't work with my data. I am taking it from an API call then doing a preload with the Len. Everything looks correct in the table view. I am still trying to modify this in order to get the unique count per field.

Re: How to find distinct data points per person

Anonymous — Fri, 13 May 2016 15:14:52 GMT

Just to add to this I exported my data out to excel and then into Qlikview to create a new QVW and it works. But I need it to work with the API call. I then made a QVD from the API call instead and it still doesn't work. I then made a new document and loaded the QVD into it and it works. I am wondering if it is because the field "Email" is a key to another table. In test documents I don't load the other table with the "Email" key.

Maybe this isn't possible to do.Just trying to wrap my head around why it doesn't work still. The formula is correct everywhere else though.