topic A distinct and chart grouping issue in QlikView

A distinct and chart grouping issue

Fri, 11 Nov 2011 11:16:27 GMT

Hi,

I have generated a set of data as follows. There is other data in the set too, but not relevant for this issue.

ID Date Week Commencing Date All records here for example have set and Validsetdate correct for the expression below.

2 1/9/11 29/8/11

3 30/9/11 26/9/11

4 21/9/11 19/9/11

5 19/10/11 17/10/11

6 24/10/11 24/10/11

5 8/11/11 7/11/11

7 10/11/11 7/11/11

What I want to do is show the line chart grouping (dimension) by week commencing date. But also I want to distinct the ID field, so ID person 5 only appears once in the chart. I'm trying to do this by this expression:

=Count(DISTINCT if(Set = 'ForThisChart' and ValidSetDate=1),ID)

What this is doing is a distinct for each of the week commencing groups in the chart. Therefore ID 5 is still appearing twice in the report, once in W/C 17/10 and once in the W/C 7/11.

Is there anyway to handle this to remove that second 5 person here? I assumed the distinct would do it, but it's not. It would be a real headache to remove the second ID from the underlying dataset!

Thanks!
Nick

A distinct and chart grouping issue

rohit214 — Fri, 11 Nov 2011 11:50:55 GMT

if i am getting you correct....

do you want to remove

5 8/11/11 7/11/11

am i right..

thanks

rohit

A distinct and chart grouping issue

Fri, 11 Nov 2011 12:01:12 GMT

Hi Rohit,

Yes, that's right. I only want the first instance of any ID that is picked up.

Re: A distinct and chart grouping issue

Fri, 11 Nov 2011 12:01:54 GMT

Hi Rohit,

Yes, that's right. I only want the first instance of any ID that is picked up.

A distinct and chart grouping issue

rohit214 — Fri, 11 Nov 2011 12:48:41 GMT

hey nick

remove if(Set = 'ForThisChart' and ValidSetDate=1)

because this condition creates problem..may be by using that condition lies on that condition

may it helps you

thanks

rohit

Re: A distinct and chart grouping issue

Fri, 11 Nov 2011 12:54:44 GMT

Thanks Rohit,

Unfortunately, I can't remove that IF statement. I need that to drop the other records in the dataset that are nothing to do with this chart (I didn't list those in the example, but there are 1000s of records this IF removes).

So I need that IF in place........

Re: A distinct and chart grouping issue

john_duffy — Fri, 11 Nov 2011 13:42:53 GMT

Hi Nick.

Not sure I fully understand the issue. Are you trying to only processes IDs with the minimum Commencing Date?

If so, how about adding a flag to the data set in the load scripts to indicate the min Comm. Date for each ID.

eg.

Load

ID, Date, Comm_Date

From Table1

;

left join

Load

ID,

'Y' as Min_Comm_Date_Flag

min(Comm_Date) as Comm_Date

From Table1

Group by ID

;

Then only process records with Min_Comm_Date_Flag = 'Y' in your expression. Note: The above code has not been tested.

John.

Re: A distinct and chart grouping issue

Fri, 11 Nov 2011 15:54:47 GMT

Thanks John,

That gave me a good idea for the SProc to bring back another date based on similar criteria to what you've said here! That gets the IDs into the same date groups on the chart so the distinct expression then works perfectly!

Cheers

Nick

Re: A distinct and chart grouping issue

rohit214 — Mon, 14 Nov 2011 04:20:20 GMT

hey....

use intervalmatch(ID,[Week Commencing Date ])

may it helps you

thanks

rohit