Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
berryandcherry6
Creator II
Creator II

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 .

1 Solution

Accepted Solutions
vinieme12
Champion III
Champion III

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)

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.

View solution in original post

13 Replies
awhitfield
Partner - Champion
Partner - Champion

What have you tried so far?

Andy

vinieme12
Champion III
Champion III

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)



Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
berryandcherry6
Creator II
Creator II
Author

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
Champion III
Champion III

yes

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
berryandcherry6
Creator II
Creator II
Author

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
Champion III
Champion III

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

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
berryandcherry6
Creator II
Creator II
Author

Hi Vineeth,

Please find below attached qvf file.

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

vinieme12
Champion III
Champion III

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

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
berryandcherry6
Creator II
Creator II
Author

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.