This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
I have a leading question, but I did find one solution that might work... Does the number of SIGNAL combinations you're looking to validate change? Or will it only be those 3 you specified? If so (or if you can specify the SIGNAL combos ahead of time in a text input, as I've done in the workflow attached), then the following might work.
1. Transpose Data (Key Field = Project)
2, Append a list of the SIGNAL combos you're looking for to every row of data in the transposed list
3. Formulas to calculate:Whether or not the Name column contains the letters in the SIGNAL column that was appended... and a column to find the check value to look for based on the number of letters contained in the SIGNAL name (i.e. SIGNAL AB = 2, SIGNAL ABC = 3)
4. Filter for only those rows that have a Name containing letters from the SIGNAL column
5. Summarize Tool to Group by Project, SIGNAL, check value, and Sum of Values column
6. Formula to show 1 if the Sum of the Values = Check Value
7. Cross Tab tool, Group By Project with SIGNAL as header and Value column as Values
I believe this would give you the results you're looking for... but if not, perhaps a little more background info might help? :)
I think this version will be a bit more dynamic. As long as you have a list of your desired signal combinations, it should do the rest for you, and the join works better than the append. I tested it with about 10K rows of fake data and 30 combos, and it completed in 6 seconds, so I don't believe performance will be an issue?
Let me know what you think. It's a bit of a squirrelly, long-winded workflow with a bunch of Transpose & Cross-Tab tools, but it should do the trick. :)
(Basic path: Parse out the signal components in the input data and the list of signal combos, join together with the assistance of Transpose & Cross-Tab, and eventually join the number of signal characters to the re-oriented data to determine if the sum of values for each signal combo by project = the number of digits required for each signal combo.)
Thanks Nicole - Any possible suggestions on how to generate the Signal Test combination list? is there a way to automate all the possible combinations up to with up to 3 Signal tests? So to automate building out the list of SIGNAL ABC SIGNAL ACD, etc?
Ok, it isn't pretty, and I'm positive it could be streamlined... but I believe this workflow will do it. Tested it for letters A through J and it appeared to work (generates 165 unique 2 & 3 letter combinations for letters A-J, unique meaning that it doesn't duplicate SIGNAL AB and SIGNAL BA, just shows results for one of them). Give it a shot, let me know if it works? This was a fun one... :)