I am trying to flag the occurrences of a data item in column [Customer] determined by column [Division] and set three values for it in column [flag].
If data item [Customer] has both ‘Q’ and ‘Z’ data item values for column [Division] in separate rows I want the value of the data item [flag] set to 2 for every repeated occurrence of data item [Customer] where customer is a member of both ‘Q’ AND ‘Z’ Division.
If this condition exists I want the value of the [flag] column equal to 2 for all rows where the [Customer] column value occurs.
If data item value [Division] for [Customer] is only ‘Q’ then set [flag] to 1 and if data item value [Division] for [Customer] is only ‘Z’ then set [flag] to 0.
There are thousands of possible values for the [Customer] column data item and multiple rows can contain the desired [customer] / [flag] combinations.
How would I create column [flag] in the example below?
I want Customer separated by unique values.
Notice Customer X is repeated 3 times and Customer A is repeated twice and the value in the [flag] column for each is 2 - ALL [flag] values where a customer is repeated In a row is 2.
DESIRED RESULTS:
Customer Rep division Count
A : 1 : Q : 1
B : 2 : Z : 0
X : 3 : Q : 2
X : 4 : Z : 2
X : 4 : Q : 2
D : 5 : Z : 0
S : 3 : Q : 2
H : 4 : Q : 1
S : 4 : Z : 2
Try this based on your expanded explanation:
CASE count(distinct [division] for [Customer])
WHEN 2 THEN 2
WHEN 1 AND [division] = 'Q' THEN 1
WHEN 1 AND [division] = 'Z' THEN 0
END
We count the distinct values of 'division' for every value of 'Customer'. If the count is 2 we know that both 'Q' and 'Z' are represented and we output the integer 2. If the count is 1 and the value of 'division' is 'Q' then we output 1. Lastly, if the count is 1 and 'division' is 'Z' then we output 0.
Assuming 'Q' and 'Z' are the only possible values for 'division' you can safely change the last test to: ELSE 0 to simplify. I included the full logic for clarity.