Unable to collapse my rows

[JPSeagull]

Hi everyone. I am stuck. I have tried various ways to get a simple dataset to pivot with product IDs across the top (which I can do), but target two products that are related to each other to get the numerator number I need. That is, if product ID 9617 is "Yes" in the Comments, then it looks to product ID 7717 for it being done within 7 days. I cannot get the formula to work:

I need all the product IDs (except 7717) to see if the salesperson had contact within 7 days and if so, to give me 1. For 7177, I am first seeing if I have to contact the customer by looking at product 9617 and if the response is "Yes" then it goes to the 7717 team to call within 7 days. I can't get that formula to work and then I can't get to 8 rows (one per customer).

The workflow attached. I am trying to get records 5 & 6 and 7 & 8 to be on one line. Any suggestions?

[BS_THE_ANALYST]

Ignore that, misread the question .. 😂

[BS_THE_ANALYST]

How do you actually want the result to look if you bring 5&6 together and then 7&8? I'm think we can replace the null with the value it's getting collapsed with? I.e. null and 0 would collapse to 0? null and 1 collapse to 1?

[JPSeagull]

Hi BS - yes, correct - the collapsing between the two rows won't affect the totals in that the 1s will remain ones, the 1 above the null will come down and, etc. 

[BS_THE_ANALYST]

One of the issues as to why it's not 'collapsing' into one row is also how you've selected the Group Data by in the Cross Tab: 

For instance, record 5&6, and record7&8. They don't have different values, i.e. one is null, and one is = 1. That's the reason why they can't collapse, as Alteryx sees them as two separate groups

[BS_THE_ANALYST]

i.e. not grouping by this option:

[JPSeagull]

Fair enough... I unchecked it and get the 8 rows clean, but need now to find a way to carry over that value since I need it to sum up the denominator for 7717. I also have to figure out how to push a 1 into the 7717 if 9617 is "yes" and the salesperson called the customer within 7 days. I have tried nesting the IF statements with no luck.

[BS_THE_ANALYST]

If there was a way for you to have a desired output for your next step, I can certainly be of more use. I'm the type who needs to physically see what the target is 😂 

[JPSeagull]

Yes, me too. So, the output would be that matrix I ingest into Tableau to build out calculation summing up the 1s by product as the numerator over the total distinct customers to get the rate of calling within 7 days. The trick is to first screen product 9617 on it's own to be called with 7 days (done) and if after that call the customer wants to speaks the sales person for the related product 7717, then it's indicated by "yes" and then the salesperson who owns 7717 is on the clock to call within 7 days. 

In the end, I was hoping to extract out of SQL the products and values (usually comments) which I easily can evaluate if each product was called on in time or not. I am just trying to figure out how I can evaluate 7717 only if it's needed and still get it to show up as a 1 if called on time, 0 if not called on time or leave as NULL if 9617 indicated that the sales person of 7717 was not needed.

[JPSeagull]

I figured out my solution - had to use the multi-row formula tool to along with sort tool to use an expression to look up one row, etc. If anyone is interested, just ask.