topic Re: MySQL to Qlikview Expression translation in QlikView

MySQL to Qlikview Expression translation

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: MySQL to Qlikview Expression translation

sunny_talwar — Tue, 09 Jan 2018 15:56:24 GMT

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

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

Re: MySQL to Qlikview Expression translation

marcus_sommer — Tue, 09 Jan 2018 15:58:59 GMT

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

Re: MySQL to Qlikview Expression translation

Anonymous — Wed, 10 Jan 2018 09:05:26 GMT

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

Re: MySQL to Qlikview Expression translation

Anonymous — Wed, 10 Jan 2018 09:06:52 GMT

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?

Re: MySQL to Qlikview Expression translation

sunny_talwar — Wed, 10 Jan 2018 09:09:04 GMT

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;

Re: MySQL to Qlikview Expression translation

Anonymous — Wed, 10 Jan 2018 09:39:47 GMT

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

Re: MySQL to Qlikview Expression translation

sunny_talwar — Wed, 10 Jan 2018 09:42:32 GMT

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)

Re: MySQL to Qlikview Expression translation

Anonymous — Wed, 10 Jan 2018 10:05:29 GMT

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

Re: MySQL to Qlikview Expression translation

Anonymous — Wed, 10 Jan 2018 10:33:13 GMT

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?

Re: MySQL to Qlikview Expression translation

sunny_talwar — Wed, 10 Jan 2018 12:03:02 GMT

May be this

Dimension

User

Date(Floor(initiated_date)) -> Calculated dimension

Expression

Count(DISTINCT initiated_date)

This is useful assuming initiated_date is a timestamp... else all you will get is 1 for each row