13 Replies Latest reply: Apr 12, 2017 12:35 PM by Vineeth Pujari

# Applying Not In for multiple Tables

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:

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

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

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

• ###### Re: Applying Not In for multiple Tables

What have you tried so far?

Andy

• ###### Re: Applying Not In for multiple Tables

1.) Count of ADClicked by CourseCompleted users only.

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 .

• ###### Re: Applying Not In for multiple Tables

HI,

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

• ###### Re: Applying Not In for multiple Tables

yes

• ###### 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

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)

• ###### Re: Applying Not In for multiple Tables

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

• ###### 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.

• ###### 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

• ###### 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.

• ###### Re: Applying Not In for multiple Tables

ok, just checked you app  <<MODIFIED>>

here you go

=count({<

*

*

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

>}

distinct UserID)

• ###### Re: Applying Not In for multiple Tables

sample app

• ###### Re: Applying Not In for multiple Tables

Hi Vineeth,

Sorry for delay Response.

It works and getting correct output.

See attached App of slightly different Requirement, i am applying same logic over here, But get wrong output.

I should get 22 in NO action.  But displays 2.

• ###### Re: Applying Not In for multiple Tables

Hi, Sorry i'm on vacation

If you open a separate thread, someone will surely assist you