Hi Team,
I would like to get some data manipulation done in alteryx.
Input:
Months | Value |
Jan | 3 |
Feb | 4 |
Mar | 5 |
Output:
Months | Value |
Jan | 3 |
Feb | 4 |
Mar | 4 |
If I run the workflow for March month using calendar,it should have value from Feb month.
Can you please help?
Thanks
Hello @Polite123
I attempted to build something that would accomplish what you're looking for. I changed the months to Oct, Nov, & Dec to test.
First I created two new fields using a formula tool.
Month Num (the number of the month from the Months field):
tonumber(substring(DateTimeParse([Months],"%b"),5,2))
Diff (the difference between the month number in the data and the current (today's) month number:
[Month Num] - tonumber(DateTimeFormat(DateTimeToday(),"%m"))
From here, just use a multi-row formula tool to update the Value field based on our new Diff field. If Diff is equal to one, update the current row with the previous row's value:
iif([Diff]=1, [Row-1:Value], [Value])
@Kenda -Thank you for quick reply.
With some modification in data,can the below be achieved?
Input data:
Months | Category | Value1 | Value2 |
January | A | 3 | 2 |
January | B | 4 | 3 |
January | C | 6 | 4 |
February | A | 6 | 7 |
February | B | 7 | 8 |
February | C | 5 | 9 |
March | A | 5 | 1 |
March | B | 6 | 2 |
March | C | 7 | 3 |
Output data:
Months | Category | Value1 | Value2 |
January | A | 3 | 2 |
January | B | 4 | 3 |
January | C | 6 | 4 |
February | A | 6 | 7 |
February | B | 7 | 8 |
February | C | 5 | 9 |
March | A | 6 | 7 |
March | B | 7 | 8 |
March | C | 5 | 9 |
If we run the workflow for the month of March,it should give values from February corresponding to each category.
Similarly if we run for April,it should give data from March and so on.
Any help is much appeciated.
Thanks in advance
Yes this is definitely possible. I think the key here is to add a sort tool before the multi-row formula tool, though. First sort category ascending then month num ascending. This way the categories are grouped together sorted by the months.
Then you'll just need two multi-row formula tools, one to update each value field.