Announcements
The way to achieve your own success is the willingness to help somebody else. Go for it!
cancel
Showing results for
Did you mean:
Partner - Contributor III

## Formula for Exclusion in set expression

I have fields namely VisitId and TCode(2 codes).

I have to calculate count of distinct VisitId where TCode is equal to {'D0039} excluding  the following code {'N0023'}

Can anyone help me out with a formula in the table chart?

Labels (4)

• ### Visualization

1 Solution

Accepted Solutions
Specialist II

@GeorgePhilips23  I have created a Script at the Back end which will only Fetch those VisitID who has done both test or just D0039 like below.

NoConcatenate
Temp:
VisitId,TCODE
174,N0023
174,D0039
046,D0039
140,D0039
140,N0023
274,N0023
417,N0023
107,D0039
];

NoConcatenate
Temp1:
Resident Temp
Where (TCODE='N0023' and TCODE='D0039') or TCODE='D0039';

Exit Script;

At the front End you can calculate . Count(distinct NewVisitID)

6 Replies
Specialist II

@GeorgePhilips23  Since your TC code has only 2 Values.

Your Set expression will look like the below. This will count distinct VisitID for TCode=D0039

Count({<TCode={'D0039'}>}distinct VisitId )

Please let me know if it worked.

Partner - Contributor III
Author

Yes, you are right.

Now, there are some VisitId's who have taken both tests while some VisitId's have taken only one of the tests.

I need to create a table consisting of only VisitId's  who have taken D0039 and N0023(both tests),

excluding VisitId's who have taken single tests.

How can this be done?

Specialist II

@GeorgePhilips23  Could you please share some sample data?

Partner - Contributor III
Author

 VisitId TCODE 174 N0023 174 D0039 046 D0039 140 D0039 140 N0023 274 N0023 417 N0023 107 D0039

Here, I want to retrieve only those data where visitid has taken either "both tests" or only D0039 and exclude those who have taken only N0023.

Thanks!

Specialist II

@GeorgePhilips23  I have created a Script at the Back end which will only Fetch those VisitID who has done both test or just D0039 like below.

NoConcatenate
Temp:
VisitId,TCODE
174,N0023
174,D0039
046,D0039
140,D0039
140,N0023
274,N0023
417,N0023
107,D0039
];

NoConcatenate
Temp1: