MySQL to Qlikview Expression translation

[Anonymous]

I've ran into a problem while writing some expressions, based on some MySQL queries, in a pivot table in QlikView. The results between the said query and the expression is completely different for most of the users but not for all.

The query is:

SELECT distinct date(initiated_date) FROM activities

where deleted = 0

and activity_type_id != 8

The expression is: Count({<activity_type_id -= {'8'}>} distinct initiated_date)

I'm counting the query results' rows for different users,users that I'm using as dimension in the pivot table. For some users the results match(the number of rows resulted in the query equals the result of the Count expression that I'm using in the pivot table) but for others it doesn't.

Why? Am I missing something regarding the count expression ? If so, how should I write the expression?

[sunny_talwar]

May be you need deleted = {0} in your set analysis also?

Count({<activity_type_id -= {'8'}, deleted = {0}>} distinct initiated_date)

[marcus_sommer]

Your SQL contained a further condition on "deleted" and the query returned just distinct dates without a regard to any users - therefore the results are different.

- Marcus

[Anonymous]

I've tried with deleted = {0} in the set analysis, no change.

[Anonymous]

I've tried the expression with and without "deleted", the results are the same in QlikView.
So how do I make the expression in QlikView disregard the users?

[sunny_talwar]

Try to use this expression in a text box object so that you can compare apples to apples... or compare the result of this query to your pivot table

SELECT DISTINCT

     user,

     date(initiated_date)

FROM activities

WHERE

          deleted = 0

     and activity_type_id != 8

Group By user;

[Anonymous]

So should I add the user field to the distinct in my QlikView expression ? The query you share seems to do the right job

[sunny_talwar]

No, what I am trying to say is that you have used  user as a dimension in your pivot table, so to replicate the same from SQL... you need to group by user in SQL....

Your QlikView will have it like this

Dimension

User

Expression

Count(DISTINCT {<activity_type_id -= {'8'}, deleted = {0}>} initiated_date)

[Anonymous]

I see. Will come back if I don't manage to find a solution to my expression.

[Anonymous]

Ok so I slightly modified the query you gave me to display the days worked.
Now it is something like:

SELECT DISTINCT user,date(initiated_date),

count(distinct date(initiated_date)) 

FROM activity

WHERE

deleted = 0

and activity_type_id != 8

Group By user;
How do I translate this into an expression?