community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Reporting differences between records with matching ID

Asteroid

I'm hitting a mental block on how to go about this.  I'm trying to report differences between several records with the same ID.  

 

  • % of ID's that had multiple records and had a change in 'data1' column
  • AVG changes in Data1 if a change occurred as a % increase/decrease

From there I can reuse the formula to Data2, Data3.

 

IDData1Data2Data3Data4FirstLast
12250044410
12255043301
13360044411
14440044401
14440044400
14445055410

 

In the data above, we care about cases when First and Last are not both '1' or '0'.  The second to last row of data would be excluded in this case along with ID 133 as both of them have double 1 or double 0.

 

As an expected output for ID 122.  We should report Data1 increased '50'.  In ID 144 we should report a decrease in Data1 by '-50' since the row with Last =1 for this ID is 400 and the row with First=1 is 450.

 

 

I set up a formula in the packaged workbook to get started. 

Alteryx Certified Partner
Alteryx Certified Partner

Hi @Shap 

 

I developed a workflow to be used as a guide. Not so sure if this is what you're looking for, but it might help you get the logic.

 

Package appended.


Cheers,

Asteroid

Would you mind screenshotting your workflow?  I'm on an older version of Alteryx 2018.4, so package wont import.

 

Thanks

Alteryx Certified Partner
Alteryx Certified Partner

@Shap 

 

See this one.

 

Change the input tool to your file path.

 

Cheers,

 

 

Asteroid

@Thableaus 

 

Thanks for this, but I'm having issues applying it to my actual data set when applied this logic to my actual workflow.  I'm not 100% sure why, but it has something to do it calculating differences not associated with the same ID. 

 

Is there another approach that would better ensure its only doing the calculation if the [ID] is identical?  If its identical, then compare [Data1] values from row record with FIRST =1 to LAST =1 to find the difference in value.

 

I tried using some transpose and crosstab functions to accomplish this, but am running into issues, simply due to lack of understanding using the tools appropriately.

 

Thanks for any help!

Labels