Problem with count returning wrong value using alternate states

[#1]

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

[Anonymous]

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

[rwunderlich]

What is the value of

count({Group_right} distinct TP_ID)

[#4]

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.

[#5]

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

[Anonymous · Accepted solution]

Have you already seen this post?

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

[#7]

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.