I have a table with two IDs, and I need to check that for a particular ID1 and ID2, all the products are the same and the same number of products.

For example in the table below, I have 10001 which has 123 and 234, and there's a line missing which is 123 having Product 2, and

for 20002, 345 and 456 both have Product 3 and 4 but, there's a difference in the last product. I need to find such cases in my data.

```ID1     ID2     Product
10001   123     Product 1
10001   234     Product 1
10001   234     Product 2
20002   345     Product 3
20002   345     Product 4
20002   345     Product 5
20002   456     Product 3
20002   456     Product 4
20002   456     Product 6```

The perfect scenario will be, which will be correct.

```ID1     ID2     Product
10001   123     Product 1
10001   123     Product 2
10001   234     Product 1
10001   234     Product 2
20002   345     Product 3
20002   345     Product 4
20002   345     Product 5
20002   456     Product 3
20002   456     Product 4
20002   456     Product 5```

Basically I need to find all the cases in my data where in a particular ID1, all the ID2's don't have consistent products, by consistent products I mean all ID2s should have the same products within an ID1.

Any suggestions on a way to find the cases in the first table?

Sep 27, 2018 in Tableau 383 views

## 1 answer to this question.

Imagine you've loaded your data into a dict, and product list is a set (this would help you guarantee that products aren't duplicated for an id1, id2, by the way):

```data = {
10001: {
123: set([1]),
234: set([1,2])
},
20002: {
345: set([3,4,6]),
456: set([3,4,6])
}
}```

Then you can check if two values for id2 have the same items by using the '^' operator on sets. Check https://docs.python.org/3/library/stdtypes.html#set. For example:

```a = data[10001][123]
b = data[10001][234]
c = a ^ b # len(c) will be >0 !!```

'^' calculatesthe symmetric difference between both sets, so it will return the empty set if and only if both sets are equal.

So you can iterate over all id2 keys for a given id1 and break with a message once '^' of it and the previous one hasn't got zero len. Example:

```for id1 in data:
last_seen = None
for id2 in data[id1]:
actual = data[id1][id2]
if last_seen != None and len(last_seen ^ actual) != 0:
print('Items for id1 {} are not equal'.format(id1))
break
last_seen = actual```

This is supposing your csv file isn't necessarly ordered so you needed to load it into a dict... If your file is ordered by ids then you can read the file and do the job at once, of course, i'm sure you can adapt this.

• 860 points

## how to get the count of non zero values in a row

In first part you should solve the ...READ MORE

## How to calculate the percent of records within a group in tableau?

You can click the measure SUM(Number of Records) and ...READ MORE

+1 vote

## Is it possible to access or install tableau desktop insde tableau server?

Try to understand this, Tableau Server and ...READ MORE

## Hover text shows numbers in decimal points

Do this: 1. Right click on the calculated ...READ MORE

## Dropdown to change x-axis in Tableau

You need to create a  str datatype parameter ...READ MORE

## Tableau Desktop- Tableau Server

No, you don't necessarily need to purchase ...READ MORE