Formula for Exclusion in set expression

[GeorgePhilips23]

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?

[sidhiq91]

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

[GeorgePhilips23]

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?

[sidhiq91]

@GeorgePhilips23  Could you please share some sample data?

[GeorgePhilips23]

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!

[sidhiq91 · Accepted solution]

@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:
Load * Inline [
VisitId,TCODE
174,N0023
174,D0039
046,D0039
140,D0039
140,N0023
274,N0023
417,N0023
107,D0039
];

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

Exit Script;

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

[sidhiq91]

@GeorgePhilips23  if it has resolved your issue. Please accept as your solution.