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

Alteryx designer Discussions

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

Find duplicates in different rows and different columns

Meteoroid

I have 2 tables as below and below is my requirement:

 

1.       Find if there are duplicates for any column in any row. For example, row1 in table1 and row1 in Table2 have ‘2’ in common.  Pull such records and generate a record combining both and removing duplicate like row1 in Table3(Final output)

2.       One condition is when combining such records if total values exceed 20, split into 2 records. Like Row4 in Table1 and Row4 in Table2 together after removing duplicates have 22 values, from Table3 you can see that it is split into 2 rows.

Table1:

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

2

3

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

1

11

56

48

22

38

 

 

 

 

 

 

 

 

 

 

 

 

 

 

81

54

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

10

9

8

7

6

5

12

13

14

15

16

17

18

19

20

21

23

25

 

 

 

Table2:

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

77

2

78

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

60

61

62

63

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

88

89

54

86

90

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

29

30

31

10

9

8

45

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Final Output(Table3):

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

2

3

77

78

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

1

11

56

48

22

38

 

 

 

 

 

 

 

 

 

 

 

 

 

 

81

54

88

89

86

90

 

 

 

 

 

 

 

 

 

 

 

 

 

 

60

61

62

63

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

10

9

8

7

6

5

12

13

14

15

16

17

18

19

20

21

23

25

29

30

31

45

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

How can I achieve this. Thanks in advance!

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

Try this approach:  

 

Use a TRANSPOSE tool to group by ROW (RECORDID will help).  This will give you RecordID plus name value pairs.  Filter out all blank values.  Repeat for table 2.  Union the data together and then CROSSTAB the data back to a single row by GROUPING on Record ID, using Name as Header and Value as Value.  In the crosstab, you can select FIRST as the method.  This will remove duplicates.  If it doesn't, then use a unique tool (all 3 fields checked) and try it using concatenate.

 

This should get you past your basic problem.  Next you'll have to deal with limits of 20 fields.

 

Cheers,


Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Alteryx Certified Partner

Hi @ssphv 

 

Not the easiest solution here, but it might work.

 

Check the workflow details and let me know if it suits you. Hard to explain, but it was quite a challenge.

 

Solution.PNG

 

Cheers,

Quasar

Hi @ssphv 

 

Wow!! What a challenge this one was. I wanted to build something that would match any value in any row or column, as per your request, which took a few transpose, joins, cross tabs, and unions. May be more convoluted than it needs to be. But since Row 5 could match to row 1, transposing and cross-tabbing back together wouldn't work.

Figuring out the "over 20 columns" was fun too. Decided to reassign numbers to the headers, and anything over 20 had the numbers restart at 1. Those records also got a letter appended to their record ID so that the data would build back in the proper order.

Please let me know if any of this doesn't make sense, and if it works for you.
image.png
cheers!

Esther

Quasar

Just tried it with different sets of numbers, and it still works :)

Meteoroid

This works perfectly and yes I forgot to mention that it should match any value in any row and any column. I appreciate your help!! Thank you!

Labels