Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
berryandcherry6
Not applicable

Applying Not In for multiple Tables

Hi,

i have 3 tables

Table1:   

UserIDCourseCompletedADClicked
3411
4310
4511
4600
4700
4810

//above table contains  CourseCompleted and Not CourseCompleted Users

Table2:   

DownloadIDUserIDdownloaded
23340
24430
25452
28483

//above table contains only CourseCompleted Users


Table3:

UserIDtypeID
347
4323
4523
4623
4723
486
455
464
4823

//above table contains  CourseCompleted and Not CourseCompleted Users

Here i Need to get below Counts:

1.) Count of ADClicked by CourseCompleted users only.

2.) Count of downloaded by CourseCompleted users only.

3.) Count of CourseCompleted users only who has typeID = 23.

4.) Count of CourseCompleted users who has not involved in any of above actions .

Tags (2)
1 Solution

Accepted Solutions
vinieme12
Not applicable

Re: Applying Not In for multiple Tables

ok, just checked you app  <<MODIFIED>>

here you go

=count({<

UserID -= {"=sum({1<CourseCompleted={1}>}ADClicked)>0"}>

*

<UserID -= {"=sum({<CourseCompleted={1}>}downloaded)"}>

*

<UserID -= {"=count({<CourseCompleted={1},typeID={23}>} DISTINCT UserID)"}

>}

distinct UserID)

13 Replies
awhitfield
Not applicable

Re: Applying Not In for multiple Tables

What have you tried so far?

Andy

vinieme12
Not applicable

Re: Applying Not In for multiple Tables

1.) Count of ADClicked by CourseCompleted users only.

sum({<CourseCompleted={1}>}ADClicked)

2.) Count of downloaded by CourseCompleted users only.

sum({<CourseCompleted={1}>}downloaded)


3.) Count of CourseCompleted users only who has typeID = 23.

sum({<CourseCompleted={1},typeID={23}>} DISTINCT UserID)


4.) Count of CourseCompleted users who has not involved in any of above actions .

count({<UserID={"=sum({<CourseCompleted={1}>}ADClicked)=0"}>*<UserID={"=sum({<CourseCompleted={1}>}downloaded)=0"}>} UserID)



berryandcherry6
Not applicable

Re: Applying Not In for multiple Tables

HI,

For 4th one,Also coursecompleted users who has no logtypeID = 23. Then will it become ??

count({<UserID={"=sum({<CourseCompleted={1}>}ADClicked)=0"}>*<UserID={"=sum({<CourseCompleted={1}>}downloaded)=0"}>*<UserID={"=sum({<CourseCompleted={1},typeID={23}>} DISTINCT UserID)=0"}>} UserID)

vinieme12
Not applicable

Re: Applying Not In for multiple Tables

yes

berryandcherry6
Not applicable

Re: Applying Not In for multiple Tables

Hi Vineeth,

For 4th expression, i am getting 0, it should be 1. Since it has no logtypeid = 23, downloaded = 0, and ADClicked = 0.

If Chage your expression to this

count({<UserID={"=sum({<CourseCompleted={1}>}ADClicked)=0"}>*<UserID={"=sum({<CourseCompleted={1}>}downloaded)=0"}>*<UserID={"=sum({<CourseCompleted={1},typeID={23}>} DISTINCT UserID)=0"}>} UserID)


This gives me Zero.

Can you explain abve expression, that is how it excludes userID which is not involved in other 3 things.



and in 3rd expression it is  Count({<CourseCompleted={1},typeID={23}>} DISTINCT UserID)


vinieme12
Not applicable

Re: Applying Not In for multiple Tables

can you post a sample please, it'll be easier

berryandcherry6
Not applicable

Re: Applying Not In for multiple Tables

Hi Vineeth,

Please find below attached qvf file.

Here userID 43 is not involved in any action and typeID = 23.

vinieme12
Not applicable

Re: Applying Not In for multiple Tables

but the 3rd expression is to filter only userid with type = 23! so it is a part of the 3rd expression therefore it is not showing in the 4th one

berryandcherry6
Not applicable

Re: Applying Not In for multiple Tables

For 3rd, to filter only userid with type = 23. This has no problem.

I am talking about 4th expression.

For 4th, i need count of userID which is not having typeID = 23, not Clicked and not Downloaded,i.e ADClicked= 0, downloaded =0, typeID other than 23.  So According to our data it should be 1 because UserID = 23 is not invovled in any action and doenot have typeID = 23.