Applying Not In for multiple Tables

[berryandcherry6]

Hi,

i have 3 tables

Table1:   

UserID	CourseCompleted	ADClicked
34	1	1
43	1	0
45	1	1
46	0	0
47	0	0
48	1	0

//above table contains  CourseCompleted and Not CourseCompleted Users

Table2:   

DownloadID	UserID	downloaded
23	34	0
24	43	0
25	45	2
28	48	3

//above table contains only CourseCompleted Users

Table3:

UserID	typeID
34	7
43	23
45	23
46	23
47	23
48	6
45	5
46	4
48	23

//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 .

[awhitfield]

What have you tried so far?

Andy

[vinieme12]

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]

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]

yes

[berryandcherry6]

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]

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

[berryandcherry6]

Hi Vineeth,

Please find below attached qvf file.

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

[vinieme12]

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]

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.