Alteryx designer Discussions

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

Multi-Row Formulas to identify First Entries - Bug in my flow

Meteor

Hi,

 

I have a bug in my process, hoping somebody could help please.

I have two fields, Color and Fruit. I made a process to only show the first entry of that column so I used a Mult-Row formula on each field to number each specific entry:

 

I'd like the final result to look like this:

 

colorfruit3.png

 

IF [Color] == [Row-1:Color]

THEN [Row-1:ColorNumber]+1

ELSE 1

ENDIF

 

IF [Fruit] == [Row-1:Fruit]

THEN [Row-1:FruitNumber]+1

ELSE 1

ENDIF

 

colorfruit1.png

 

I then used another two Multi-Row formulas to update the fields by removing numbers greater than 1:

 

IF [ColorNumber] > 1

THEN NULL()

ELSE [Date]

ENDIF

 

IF [FruitNumber] > 1

THEN NULL()

ELSE [Country]

ENDIF

 

colorfruit2.png

 

 

 As you can see above this works for Color but when there isn’t a change of Fruit for a new Color then this doesn’t work (highlighted cells). How could I change the formulas to make this work?

 

Thanks.

Highlighted
ACE Emeritus
ACE Emeritus

A slight addition to the formula is all that's needed:

Highlighted
Meteor

So simple, should have seen this! Thanks pcatterson!

Highlighted
Bolide

3 other methods to provide the same solutions using different tools.  Enjoy.Using Tiles and multi-rowUsing Tiles and multi-rowUsing Unique and multi-fieldUsing Unique and multi-fieldUsing Summarize, multi-row and multi-fieldUsing Summarize, multi-row and multi-field

Highlighted
Meteor

Hey Alex!

 

Your multiple solutions are always so elegant and interesting  to work through. I've learnt multiple new tools and methods in approaching problems by following you.

 

I'm in awe of your skills. How long have you been using Alteryx?

 

Many thanks as always Alex :)

Highlighted
Bolide

Thanks again.  I've been using Alteryx for just under 2 years on a sporadic basis.  I'm a self-employed fractional-CFO / data analyst consultant so not all my engagements require Alteryx.  I learn a lot from helping others and looking at others solutions.  The community members and articles are a great help.  Being an self-identified Excel expert :>, I think about how I would go about a solution in Excel and see how that would look in Alteryx and then utilize a similar process.  I also work backwards from how the solution needs to configured and go from there.  Hope that helps you on your tasks.  Enjoy!

Labels