Checking for Groups of Products Purchased per Customer

[junker]

I have a question and I hope it's not too challenging for the experts out there...

I have a .yxdb database file with all of my individual transaction records that I use the summation tool on to Group By "Customer" and Group By "Product" then Count Non-Null which gives me a list like this:

Customer     Material     Customer_Material Count

"1"                "A"             1

"1"                 "B"            10

"1"                 "C"            5

"1"                 "Z"            1

"2"                 "B"            2

I want to create a flag field called ABC Group Flag that turns to 1 for each customer if they bought any quantity of A B and C. I'm going to create another one called XYZ Group Flag that turns to 1 if an individual customer purchased any amount of X Y and Z that would look like below:

Customer     ABC Flag     XYZ Flag

1                  1                   0

2                  0                   0

...

I'm realizing the Formula Tool isn't sufficient for this??? Is this something for the Multi-Row Tool?

I'm basically wanting to count up how many GROUPS each customer has purchased to determine a discount level.

I'm GREATLY appreciate any advise on how to tally groups. No problem handcoding the groups with conditional statements etc but I'm just stumped.

Thank you in advance!!!

[kelsey_kincaid · Accepted solution]

Hi @junker 

You could use the Summarize tool to Group by Customer and Concatenate the Materials. Then use a formula tool to check whether the concatenation of all of the materials contains 'A,B,C' or 'X,Y,Z'

[junker]

This is so good! Thank you so much!!! This will definitely work. 😃

Could I throw one variation off at you? What if we wanted to be able to identify any one of the group (A B C) or (X Y Z) to flag it...

Would this be how you would suggest using Concatenate? I think this will definitely work well if I go this route.

[kelsey_kincaid]

Hi @junker - always happy to help! Yes, the formula you showed should work.