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

Add an ascending number to rows, which will stay the same if the last row was repeated

AkisM
10 - Fireball

My excel table looks like this:

 

Counter                 ProductDescription
1                            Bed
2                            Chair
3                            Chair
4                            Table
5                            Chair
6                            Table

 

I want every time it finds the same consecutive description, to not increase the counter. So the output should look like:

 

Counter                 ProductDescription
1                            Bed
2                            Chair
2                            Chair
3                            Table
4                            Chair
5                            Table

 

I used a MultiRow formula tool with the "Update Existing Field" set to [Counter], and my expression is:

IF [ProductDescription]=[Row-1:ProductDescription] THEN ToNumber("1") ELSE ToNumber("[Counter]+1")
ENDIF

 

But it gives me endless 0s and 1s. I used the "ToNumber" function because I was getting an error about a string being entered into a numerical field or something. Counter is an integer and productdescription is a string.

5 REPLIES 5
danilang
19 - Altair
19 - Altair

Hi @AkisM 

 

You're close with your formula

 

Try this modification

 

IF [ProductDescription]=[Row-1:ProductDescription] THEN ToNumber("Row-1:[Counter]") ELSE ToNumber("Row-1:[Counter]")+1
ENDIF

 

If the previous description is the same as the current then use the previous counter.  Otherwise, add 1 to the previous counter

 

Dan

LordNeilLord
15 - Aurora

Hey @AkisM 

 

Try something like this:

 

If [Field2]=[Row-1:Field2] Then [Row-1:Field1] Else
ToNumber([Row-1:Field1])+1

Endif

AkisM
10 - Fireball

Hey, thanks for the help. Unfortunately even after trying the above formula I still get an endless series of 1's and 0's, as well as some conversion errors "Lost information in corvesion". When I try @

 

MarqueeCrew
20 - Arcturus
20 - Arcturus
Here's what to do (blindly blabbering):

Create a new field with your multi-row formula as an int16.

IF
[counter] = 1 THEN 1 ELSEIF
[product description] = [Row-1:product description] THEN [Row-1:New Field] ELSE
[Row-1:New Field] + 1
ENDIF

cheers,

Mark
Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
danilang
19 - Altair
19 - Altair

@MarqueeCrew wrote:
... (blindly blabbering)...

That's how I get through my day.

 

Dan

Labels