Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Can we combine rows based on multiple criteria's?

pri_GIS
7 - Meteor

I need to combine rows , if all the 3 criteria’s meets

  • In Field 1 (ABC=ABC)
  • To Field from first row= From Field of next row 
  • In Field 2 (Row 2=3)                                                                                                                                                            Input-

Field1

From

To

Length

Field2

ABC

2518.9

6002.1

3483.22

Texas

ABC

6002.1

6542.8

540.3

Texas

CDE

1234

4234

456

WY

EFG

2234

8234

123

WY

 

Output looking for is -one row with sum of length and first of From value and last of To value.

Field1

From

To

Length

Field3

ABC

2518.9

6542.8

4023.52

3

CDE

1234

4234

456

3

EFG

2234

8234

123

3

 

I am very new to Alteryx and not sure even if this is possible?

 

Thanks,

 

6 REPLIES 6
Treyson
13 - Pulsar
13 - Pulsar

I am sure this is possible, but I am a little lost on the formatting of your question. How did we get the number 3 for field2?

 

I would take a look at the multi-row tool. This allows you to compare values in/or across columns.

Treyson Marks
Senior Analytics Engineer
ncrlelia
11 - Bolide

Hi @pri_GIS,

 

Agree with Treyson that the 3rd rule is unclear and Multi Row tool will suit your requirement.

 

  1. Using Multi Row tool and the first 2 rules to create an ID
    • ncrlelia_1-1631157509322.png

       

  2. Then using the Summarise tool.
    • Group by [ID], [Field 1].
    • Take First of [From] field
    • Take Last of [To] field.
    • Sum [Length].
    • ncrlelia_2-1631157626017.png

 

Please see attached my suggested solution. You can apply the 3rd row in the Multi Row tool.

Hope it helps.

 

Cheers,

Lelia

pri_GIS
7 - Meteor

Thanks Treyson for your response.

 

My bad. It should have been Field 2 (Row 2=3).

 

pri_GIS
7 - Meteor

Thanks Lelia for your help. 

 

I will try this solution.

csmith11
11 - Bolide

Since you are new to Alteryx, I'd like to ask if we can simplify or maybe just rephrase the 3 criteria you are using.

 

I'll also clarify our understanding of the question by adding to your data set:

 

Field1FromToLengthField2
ABC2518.96002.13483.22Texas
ABC6002.16542.8540.3Texas
ABC6542.87000457.2Texas
CDE12344234456WY
EFG22348234123WY

 

Is this what your new output would look like?

Field1FromToLengthField2
ABC2518.970004480.72Texas
CDE12344234456WY
EFG22348234123WY

 

If so we can simplify the ask by doing the following?

 

Group each record where field 1 and field 2 match

Return the First of a given group for the From Field

Return Last record of a given group for the To Field

Return the Sum of Length Field.

 

 

 

If this is what you are looking for, you'll be able to accomplish this using a single summarize tool:

 

csmith11_5-1631191715766.png

 

 

This is a much less complicated solution for what I understand your true criteria is:

csmith11_4-1631191503794.png

 

As a last note, you can substitute Min or Max for First or Last depending on your data. Please let me know if you'd like me to elaborate on this.

 

If this solution helped, please consider liking it and/or marking as the accepted solution.

 

 

 

 

 

 

 

 

   *   

pri_GIS
7 - Meteor

Lelia this was a great help. This tool worked like wonder.

 

You are awesome! 

 

Thank you so much.

Labels