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

Iterating through rows (Remember a variable for next Row)

asadee
5 - Atom

Hi, 

 

I have this data (example)

 

DateIDFieldAmount
24.09.20181Expenditure-5.00
25.09.20182Income100
26.09.20183Expenses-3.00
20.10.20184Income200
21.10.20185Expenses-4.00

 

and want to create this result

 

DateIDFieldAmountMonth
24.09.20181Expenditure-5.00September
25.09.20182Income100October
26.09.20183Expenses-3.00October
20.10.20184Income200November
21.10.20185Expenses-4.00November

 

So i want to create a new Field Month, which is based on a certain transaction (Income) which is for me the new Starting Point of a month. All the transactions coming into this period until next income Should be in the month. 

2 REPLIES 2
danilang
19 - Altair
19 - Altair

Hello @asadee

 

The trick here is to use the multi-row formula tool to generate the Month column based on what has previously occured

 

First create a DateForSort field which is an Alteryx date. Then fill all the Month information you can.  At this point you have the correct Month for all rows that fall after the 1st Income row.  You're still missing any expenses that come before this row.  You fix this by sorted the list by date descending and then filling in the blank Months which are now at the bottom of the list.  Sort Ascending and clean up the extra row and you're done

 

Solution.png

Dan

asadee
5 - Atom

Thanks. This is great!!! I was experiencing with it. It is pitty, that alteryx doesn't show "Autosuggest" during Coding in Multiple Field Row. 

Labels