Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Contributor II

Expression from SQL query

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?

1 Solution

Accepted Solutions
Highlighted

Re: Expression from SQL query

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

View solution in original post

12 Replies
Highlighted

Re: Expression from SQL query

May be this?

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
Valued Contributor III

Re: Expression from SQL query

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?



Highlighted
Contributor II

Re: Expression from SQL query

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

Highlighted

Re: Expression from SQL query

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

Highlighted
Contributor II

Re: Expression from SQL query

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

Highlighted

Re: Expression from SQL query

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?

Highlighted
Contributor II

Re: Expression from SQL query

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?

Highlighted

Re: Expression from SQL query

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)

Highlighted
Contributor II

Re: Expression from SQL query

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.