Alteryx designer Discussions

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

Isolating field discrepancies between records with the same ID?

Meteor

Hello All!

 

Curious if anyone knows of a way to isolate data discrepancies when record IDs are grouped and summarized? My apologies in advance because I don't know what a good output would even look like in the solution to this problem.

 

I am summarizing lists where I group by [Record Type] and use the “first” occurrence on a sorted sheet to ensure that I summarize to the parent data on my output – however, does anyone have a creative way to isolate what records have discrepancies of the fields being summarized? 

 

Say the below is the input file:

 

Table (A)

Record IDRecord TypeNameDataBillable
1ParentJim Store Primeabc5
1ChildJim Store Aabc5
1ChildJim Store Bdef10
2ParentFred Store Primexyz10
2ChildFred Store Axyz10
3ParentHank Store Primenop5
3ChildHank Store Aqrs10

 

Turns into this:

 

Table (B)

RecordRecord TypeNameDataBillable
1ParentJim Store Primeabc20
2ParentFred Store Primexyz20
3ParentHank Store Primenop15

 

This is where I've hit a creativity mental block...

 

Is there a good/viable/creative way to call out the Child accounts in Table (A) that don't match the [Data] field of their parent (an example being Jim Store B [Data] is 'def' rather than 'abc')?  My goal is to be able to locate the Parent and Child [Record Type] accounts that have a different [Data] value to begin the process of correcting them in an external system.

 

I sincerely appreciate the help and insights you all provide on a daily basis! Thank you!

Highlighted
Quasar

 

 

Not sure I completely understand the requirement. See attached for an example flow though. If this is insufficient I'm confident that something can be done with the multi row formula tool.

 

Cheers,

Iain

 

2018-06-12_15-56-31.png

Highlighted
Quasar
Quasar

In your Summarize tool you could add a Count Distinct on your Data field, for example:

 

test count.png

 

Then you can add logic to text for when that count is greater than one. In the attached, I used a Message tool.

Highlighted
Meteor

This is such a simple way to identify them in summary, thank you!

 

Do you think there is a way to have it point out which child is the "incorrect" one, like in the case of [Record ID] 1? 

Highlighted
Quasar
Quasar

A Join tool could pull those records out:

test count2.png

 

An you can use another Join to pull in the Parent data as well:

test count3.png

 

see the attached for details.

 

Labels