topic Re: Show Distinct Values in Chart in QlikView

Show Distinct Values in Chart

Wed, 25 Nov 2020 16:16:04 GMT

Hello,

I created this chart as below. The data model was built from two tables "Case" and "Report", linked with Case_ID as primary key. Case_ID and Report_ID are dimensions, the rest of data fields are expressions.

Since there are NULL values in the Case_ID, so I have the expression =If (not IsNull([Case Number]) AND not isnull(Report_ID),[Case Number]) for Case_ID.

There are duplicated values in "Report_ID" field. I wasn't sure why those duplicated values exsit as there is no duplicated value in the table "Report". I would like only show distinct values which not only the chart will look better and clean, as well as I will have an accurate values in "Submission Count".

Case_ID	Report_ID	Submitted Date	Submission Count
2003-0509	60303	5/9/2003	1
-	60303	5/9/2003	1
2004-0615	60506	6/15/2004	1
2005-0820	80504	8/20/2005	1
-	80504	8/20/2005	1

tried this in script (while loading data fields from Report table), but doesn't work.

LOAD
Distinct REG_REPORT_ID ,

REG_REPORT_ID as Report_ID.

I am not sure if try =count({$<[Submission Count] = {'1'}>} >} distinct Report_ID) would work for this case. Becuase I also want to eliminate those duplicated rows.

What will be your suggestion to help me solve this issue?

Thanks for your help in advance,

Becky

Re: Show Distinct Values in Chart

Anonymous — Wed, 22 Jun 2016 14:54:49 GMT

Hi Becky,

Can you share your qvw and data files with us ?

Re: Show Distinct Values in Chart

florentina_doga — Wed, 22 Jun 2016 14:57:31 GMT

use in chart

aggr(count(DISTINCT if(len(Case_ID)<>0,Case_ID)),Report_ID,SubmittedDate)

Re: Show Distinct Values in Chart

Anonymous — Wed, 22 Jun 2016 15:03:08 GMT

Hi yun,

Why do you have same report_id on one null case and one not null case?

I suggest you to hide null values:

Regards!

Re: Show Distinct Values in Chart

Anonymous — Wed, 22 Jun 2016 15:07:39 GMT

This seems strange when you mention there are no duplicates in your report table. Can you share your application as there may be an issue with any join in your script which is causing the duplicates in your data model.

Re: Show Distinct Values in Chart

Wed, 22 Jun 2016 15:09:44 GMT

Thank you Manuel,

I have the same Report_ID on one null case and one not null case? ...Where I did it?

I remember that I tried "Suppress When value is Null" but doesn't work that time.

But I am glad it working now.

Best,

Becky

Re: Show Distinct Values in Chart

effinty2112 — Wed, 22 Jun 2016 15:14:20 GMT

Hello,

I'm guessing that it's only in the Report source table you have null Case_ID. If so I would load your Report table with the line

Load

if(isnull(Case_ID),'Missing', Case_ID) as Case_ID

Then you can select 'Missing' as Case_ID and you can investigate the Reports that have no associated Case.

Alternatively you can omit these orphans altogether by adding a suitable WHERE clause to your load script for the Reports table. WHERE Exists(Case_ID) will ensure only reports with a case will be loaded.

The biggest problem you have, I would suggest, lies not with the chart but with the source tables.

Good luck

Andrew

Re: Show Distinct Values in Chart

Wed, 22 Jun 2016 15:15:53 GMT

Thanks Brain for your response. I tried Manuel's way just click the "Suppress When value is Null" which didn't work before. Then it works... not duplicated values and no null values...

Re: Show Distinct Values in Chart

Wed, 22 Jun 2016 15:16:14 GMT

Thanks Filip for your response. I tried Manuel's way just click the "Suppress When value is Null" which didn't work before. Then it works... not duplicated values and no null values...

Re: Show Distinct Values in Chart

Anonymous — Wed, 22 Jun 2016 15:17:00 GMT

Hi Yun,

You have 60303 report ID for 2003-0509 Case ID and Null Case ID. The same for 80504 Report ID.

Regards!

Re: Show Distinct Values in Chart

Wed, 22 Jun 2016 15:19:39 GMT

Oh, I see. That was the problem I had. And I am not sure why this happened. Because the same Report_ID should have same Case_ID. Then it shouldn't have duplicated values...