Multi Field Updates Based on Selected Row Conditions

[msoysal]

Hello,

I am having hard time to perform multi-row / multi-field operations for the case I described below. I am not even sure if it can be done in Alteryx without too much of an effort. Your thoughts / help is well appreciated. Thank you in advance!

I have a data set consists of 3 Products, A, B and C for 2 years of monthly data (01/2016 to 12/2017) showing 3 levels of status, Low, Medium and High.

Each row shows the status of the related product for that month of the year. The null fields represent absence of a status, which is not important.

I am trying to update the prior 2 month’s statuses (prior month and a month before that) with the current month’s status for each Product. E.g.- Product B was at Medium status on March 2016 (that is represented by “1” in the Medium column, where the Low and High read “0”), and I need both February 2016 and January 2016 to show the status Medium too for the product B. Similarly, if the month is January or February, the related status of last year’s December and October to be updated.  

An excerpt from the original table;

Product Year      Month  Low       Medium High

B            2016      8            [Null]    [Null]    [Null]

C            2016      8            [Null]    [Null]    [Null]

A            2016      9            1            0            0

B            2016      9            [Null]    [Null]    [Null]

C            2016      9            0            1            0

A            2016      10          [Null]    [Null]    [Null]

Desired outcome for the excerpted rows above

Product Year      Month  Low       Medium High                  

A            2016      7            0            1            0                           existing record

A            2016      7            1            0            0                           new record to be added

A            2016      8            1            0            0                           existing record to be updated

…                                                                                                 

…                                                                                                 

C            2016      7            0            1            0                           existing record to be updated

C            2016      8            0            1            0                           existing record to be updated

…                                                                                                 

…                                                                                                 

B            2016      8            [Null]    [Null]    [Null]                  

C            2016      8            [Null]    [Null]    [Null]                  

A            2016      9            1            0            0                          

B            2016      9            [Null]    [Null]    [Null]                  

C            2016      9            0            1            0           

[Claje · Accepted solution]

Maybe I'm oversimplifying this, but if I understand it right, you want to take a dataset that has data for each month, and create a record 1 month and 2 months back with the exact same data?

If so, I have a workflow that will help, and you don't need to use Multi-row or Multi-field to accomplish it.  I split out formulas into individual tools so you can see how it flows, but you could easily consolidate this.

The basic principle of this design is that you want a row of data that's exactly the same for the prior month, and two prior months, so therefore we can use alteryx to simply duplicate these lines of data.

This may still be missing some null values or future date values as I did not spend a lot of time thinking about final reporting format, but I think it will work in your sample data for 1/2016 through 10/2017

[msoysal]

Wooow, it's a fantastic solution with a brilliant logic. You've simplified and explained the process very well. Thank you!!!