Data exclusion

[Scheruku]

Hello All,

We are trying to exclude the data if there are is one code "99" and any other code "xx" within the same month for each no. Attached the file ad below the output. Please help.

Output:

No	Month	CD
131123345555-B11111111	Feb	99
131123345555-B11111111	Mar	99
131123345555-B11111111	Apr	99
131123345555-B11111111	May	99
131123345555-B11111111	July	99
131123345555-B11111111	Aug	99
131123345555-B11111111	Sep	99
131123345555-B11111111	Aug	99
131123345555-B11111111	June	99
138888888888-B29999999	Mar	57
133333333333-B21333333	May	99
133333333333-B21333333	July	99
133333333333-B21333333	Aug	99
133333333333-B21333333	Sep	57
133333333333-B21333333	Sep	99
135555555554-B223444444	Apr	99
131123345555-B11111111	June	99

[MarqueeCrew · Accepted solution]

@Scheruku,

One way of doing this is:

1. SUMMARIZE data with
   1. Group by No & Month
   2. Count CD
2. FILTER Count >1
3. JOIN the original data to the FILTER output (True) on No & Month
   1. Connect the L anchor to the next tool (e.g. BROWSE)

You will use the join to filter out the rows where there are multiple different CD values.  Perhaps you will want to use COUNT DISTINCT in the summarize tool in case you allow for multiple equal CD values.  Also, you will want to include YEAR in the process if you have more data.

Cheers,

Mark