Hi guys, some help on the approach to this problem would be appreciated. It's a market basket analysis type problem but a more simplistic approach to the real thing.
I have a list of policy numbers and associated Referral Codes. Each policy number can have one or more different referral codes (up to a max of 14) and there are over 100 different referral codes. (What the referral codes actually mean isn't really relevant). Each referral code is represented as one row on the data so if a policy number has more than one referral code, I'll have the policy number repeated in column A and the list of referral codes in column B. See the image which might explain it better.
What I need to analyse is the relationship between the different referral codes. So if for example code 1241 appears on a policy , how many times do the other codes appear with that codes or in conjunction with that code and other codes and in that way try and establish a likelihood that if a code does appear that another code is likely to appear. Likewise for the all the other codes, I need to try and be able to pick a referral code and be able to see what other codes appear with that code.
I can solve this problem with array formulas in Excel but I need to try and bring the solution into Tableau and automate the analysis in some way.
Is there any combination of logic formulas that you guys can think of to make this work?
I have a list of policy numbers and associated Referral Codes. Each policy number can have one or more different referral codes (up to a max of 14) and there are over 100 different referral codes. (What the referral codes actually mean isn't really relevant). Each referral code is represented as one row on the data so if a policy number has more than one referral code, I'll have the policy number repeated in column A and the list of referral codes in column B. See the image which might explain it better.
What I need to analyse is the relationship between the different referral codes. So if for example code 1241 appears on a policy , how many times do the other codes appear with that codes or in conjunction with that code and other codes and in that way try and establish a likelihood that if a code does appear that another code is likely to appear. Likewise for the all the other codes, I need to try and be able to pick a referral code and be able to see what other codes appear with that code.
I can solve this problem with array formulas in Excel but I need to try and bring the solution into Tableau and automate the analysis in some way.
Is there any combination of logic formulas that you guys can think of to make this work?