Pearson Correlation Tool Breakdown

[Mario36]

Can someone help me with the breakdown of the Pearson Correlation Tool ?
On opening the deprecated tool of Pearson Correlation, the one for Spearman's Correlation gets open.

[CristonS]

hi @Mario36 the macro version of the Pearson tool was replaced with a non-macro version. What are you looking for specifically? Right click the palette to Show Deprecated Tools - do you see one or both versions?

If you're looking for the deprecated version specifically, do you see it in this folder (for a standard install)?

C:\Program Files\Alteryx\bin\RuntimeData\Macros\PearsonCorrCoeff.yxmc

Can you navigate to the One Tool Example, either through the Help menu, or clicking on the link in the tool tip?

If I'm missing the point, please give me some more detail (what Alteryx version you're using, etc) and I can check more thoroughly, thanks.

[Mario36]

Hi @CristonS ,

I did check the deprecated tools but unfortunately the workflow is not the one of Pearson Correlation and looks like the Spearman Correlation. The Interface Tools are a clear example of the same.

[OllieClarke]

Hey @Mario36 

Like @CristonS said the Pearson Correlation tool got updated, so its interface changed. The Deprecated version has a much more similar interface to the spearman correlation tool. But if we compare their effects on the same dataset, we can see that the new and old pearson tools give a different output to the spearman tool. 

Sorry if I've misunderstood, but does this help in any way?

[Mario36]

Hi @OllieClarke ,

I totally understand what you're explaining but let me point out an issue where Alteryx suffers. I have attached a workflow which explains my issue.

Taking an example of 'Group8', this field has only 5 data point when compared to the other 12 Groups which have 36 data points. The actual Correlation calculation for Group8 across the other 12 Groups should consider only these 5 data points and not 36. What Alteryx does is, it backfills the blank 31 data points with zeroes and considers the 'Number of observations' to be 36 instead of 5.

Within the workflow, the excel data file contains a 'Correlation' tab which gives the actual and desired numbers. Grateful if you could help me achieve this result.

[OllieClarke · Accepted solution]

Hey @Mario36 How about this:

Using the old tool I did a bit of data prep first and then got the same output as excel:

[Mario36]

Hi @OllieClarke ,

This looks good. Thanks for this solution.

Could you please explain the logic behind using the Append Tool and the three Filter Tools there on ?

[OllieClarke]

Sure @Mario36

The deprecated pearson tool only allows you to select 2 fields to correlate. So I transposed the data and then appended it onto itself. This joins every row onto every row. I then filter on when the 2 dates are the same (I guess I could also have joined date=date instead of appending).

Then because I will have group1 against group8 as well as group8 against group1, I filter on group <= source_group so I end up with a unique list (in this case removing group8 against group1), but keeping group1 against group1.

Finally I remove any rows which have a null in either value or source_value. 

Then I can create a grouping by field of e.g. 1_2 which I group by in the deprecated pearson tool. 

Hope that helps,

Ollie

[Mario36]

@OllieClarke . This is brilliant. Thanks a ton for explaining and getting this sorted.

[OllieClarke]

@Mario36 glad I could help 🙂