topic Re: Expression from SQL query in QlikView

Expression from SQL query

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

I'm trying to get an expression to do the same thing in a pivot table as an SQL query.

The dimension I'm using in the said pivot table is user_id.

So, the query is:

SELECT DISTINCT user_id,date(initiate_date),

count(distinct date(initiate_date))

FROM Activity

WHERE

deleted = 0

and type_of_activity_id != 8

Group By user_id;

I've tried with:
Count({< type_of_activity_id -={8}, deleted = {0} >}, distinct initiate_date) but the results of the said expression are not correct for some users.
My question is: Is there any other way of writing the said expression? Am I missing something?

Re: Expression from SQL query

Anil_Babu_Samineni — Wed, 10 Jan 2018 12:02:57 GMT

May be this?

Sum(Aggr(Count({< type_of_activity_id -={8}, deleted = {0} >}, distinct initiate_date), user_id))

Re: Expression from SQL query

mdmukramali — Wed, 10 Jan 2018 12:05:01 GMT

Hi,

Dimension: user_id

=Count({< type_of_activity_id -={8}, deleted = {0} >}Distinct initiate_date)

looks correct.

What is the format of the initiate_date?

share the sample file or screenshot the initiate_date?

Re: Expression from SQL query

Anonymous — Wed, 10 Jan 2018 12:22:58 GMT

initiate_date's format is datetime. Could this be the issue?

Re: Expression from SQL query

sunny_talwar — Wed, 10 Jan 2018 12:23:35 GMT

My response here:

MySQL to Qlikview Expression translation

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

Re: Expression from SQL query

Anonymous — Wed, 10 Jan 2018 12:26:27 GMT

initiated_date is datetime format. And yes, I am getting 1s for each row.

Re: Expression from SQL query

sunny_talwar — Wed, 10 Jan 2018 12:28:06 GMT

So for a single user, do you have more than one timestamp in a day? For instance user xyz have 01/10/2018 8 am and 01/10/2018 10 am?

Re: Expression from SQL query

Anonymous — Wed, 10 Jan 2018 12:32:49 GMT

Yes, there is a possibility that one user may have multiple timestamps in a day. Now that I look in the pivot table, there are users with more than one timestamps in a day and I assume my Count expression is counting the multiple timestamps too.
So the issue is with the initiated_date formatting?

Re: Expression from SQL query

sunny_talwar — Wed, 10 Jan 2018 12:35:42 GMT

What is the issue? I am not sure I understand?

You want to see user and Date and count the number of timestamps for a user on a date, right? then this should work?

Dimension

User

Date(Floor(initiated_date)) -> Calculated dimension

Expression

Count(DISTINCT initiated_date)

Re: Expression from SQL query

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

Sorry, I probably didn't expressed myself properly.
I want to see user and count the distinct number of dates, disregarding the time component (I'm doing it in the loading script), for each user, in the pivot table.

Re: Expression from SQL query

sunny_talwar — Wed, 10 Jan 2018 13:17:30 GMT

Then try this

Dimension

User

Expression

Count(DISTINCT Floor(initiated_date))

Re: Expression from SQL query

sunny_talwar — Wed, 10 Jan 2018 13:20:06 GMT

disregarding the time component (I'm doing it in the loading script)

How are you doing this? Are you using Just Date() function or Are you using Date(Floor()) functions? The reason I ask this is because a lot of the time users think that Date() function will truncate the time component, but it is not true. Date function is just a formatting function... it will display you as a date, but the underlying will still have time in it. In order to remove time, you need to use Floor() or you can use DayName() function to get rid of the time part and give it a format all at once

Re: Expression from SQL query

Anonymous — Wed, 10 Jan 2018 13:31:49 GMT

......I was just using the Date() function. God, thanks a lot man, you got me out of a problem I had to deal with for a couple of days!