topic Re: Problem with count returning wrong value using alternate states in QlikView

Problem with count returning wrong value using alternate states

Thu, 29 Jan 2015 12:36:33 GMT

Hello everyone!

Should start with saying that Im kinda new to Qlikview.

I have som problem with using the count() function on two alternate states.

The following code is used:

(1) count({Group_left * Group_right}distinct TP_ID)

and

(2) count({Group_left} distinct TP_ID)

where Group_left and Group_right are alternate states of the same data.

My problem is that (1) and (2) returns different values, 100014 and 100017 respectively, when they should return the same. In my alternate states I have got one table, customer, which contains ID and some other columns. The customer IDs are loaded from two different tables in a SQL database as follows,

Customer:

LOAD * ;

SQL

SELECT

[Kund_Id] AS [ID],

...

FROM

[customer];

Recipient:

LOAD * ;

SQL

SELECT

[RecipientId] as [ID]

FROM

[Recipient];

Its this step, which adds new IDs from the Recipient table, that is the source of the problem in my opinion (since three IDs are unique from the Recipients table). Is there any possible workaround this problem that I have?

Thanks in advance,

Mattias

Re: Problem with count returning wrong value using alternate states

Anonymous — Thu, 29 Jan 2015 14:50:46 GMT

Hi Mattias,

are you sure that 1) and 2) should give the same result?

1) is an intersection, so it seems correct that value is smaller than 2)

Elena

Re: Problem with count returning wrong value using alternate states

rwunderlich — Thu, 29 Jan 2015 15:03:40 GMT

What is the value of

count({Group_right} distinct TP_ID)

Re: Problem with count returning wrong value using alternate states

Thu, 29 Jan 2015 15:08:54 GMT

Thanks Elena for the answer!

Yes, it should indeed not return the same result if the selections for the two states are different. Forgot to mention that the selection is over the total data set in the both states.

Re: Problem with count returning wrong value using alternate states

Thu, 29 Jan 2015 15:44:44 GMT

Its the same as for count({Group_left} distinct TP_ID), 100017.

Re: Problem with count returning wrong value using alternate states

Anonymous — Thu, 29 Jan 2015 15:49:10 GMT

Have you already seen this post?

http://community.qlik.com/message/413697#413697

Re: Problem with count returning wrong value using alternate states

Thu, 29 Jan 2015 16:15:32 GMT

Thanks, that helped!

With the following,

Count({Group_left <TP_ID=P({Group_right} TP_ID)>} TP_ID)

it returns the expected count value of 100017.