Manipulating and splitting Data into additional row.

[JLEE038]

Hi all - I have consolidated my data into a single source. However, I would like to manipulate and split the data provided by the supplier into individual months: 

- Currently different supplier sent the data in different format. I am facing difficulty in splitting the data into additional row. Any suggestion on how I could manipulate the data ? Thanks ! 

For Supplier B - I could think of splitting the months after each ","  >> thereafter do a count  >>and divide the quantity by the count > Then split into individual row. 

For Supplier A - Facing difficulty to split the data into individual months as they provided data from start to end of month. 

Current Raw Data : 

Quantity	Term	Supplier
5000	2020-07-01  to 2020-09-30	A
1000	2020-04-01  to 2020-04-30	A
15000	Jul 20, Aug 20, Sep 20	B

Trying to manipulate date to : 

Supplier A : To split the data for Supplier A to individual Month - Qty remain the same 

Quantity	Term	Supplier
5000	2020-07-01	A
5000	8/1/2020	A
5000	9/30/2020	A

For Supplier B : To split Data for supplier into individual month ( Total Quantity will have to be divided by the number of months split )

- Per example above raw data comes in a total qty for 3 months. I would like to split data into individual rows with qty divided by the number of months that was split. 

Quantity	Term	Supplier
5000	20-Jul	B
5000	8/20/2020	B
5000	9/20/2020	B

[Aaron_Harter]

Hi @JLEE038, 

You may want to Filter Suppliers A & B into separate streams, then parse to isolate your data points and perform your calculations:

 

[JLEE038]

Thanks very much  Aaron ! It works for Supplier B !

For Supplier A, I would like to break down the order to individual months / row. To help to achieve this, I have add an additional column "Order" so that I could have a unique identifier for each "Order". 

- For Order 1 : The input Term is 2020-07-01 to 2020-10-30 ( This would meant the order is for the month of July, Aug, Sept and Oct 2020)

- For Order 2 : The input Term is 2020-04-01 to 2020-04-30 ( This would represent the order is for the month of April 2020 )

Could you advise if there is a loop function within Alteryx ? My idea to break down the data to the Individual row  to achieve the Ideal Result as per table follow :

- If Order and DateTime_Out2 repeats, it will delete one of the row. ( Hence, I will be able to obtain 1 order row for April )

- Else  > Breakdown DateTime_Out2 to two column MM ( First two number from DateTime_Out2 ) and YYYY ( Last 4 umber from DateTime_Out2 )

- Filter for Order and Use a formula to deduct the difference between the MM then minus 1  ( Eg: 10 -7-1= 2) - Create a new column "n"

- Then use a loop function and add "n" to the smallest number in MM for each Order until n reaches 0>> 7+2 =9, then 7+1=8 and create two additional row 

Would you be able to advise how to implement the above idea or is there a better way to execute it  ? Trying to figure out how to do a loop function within Alteryx. 

Ideally -The end result will be  like the below table : 

Ideal Result : 

Quantity	Supplier	Unique Identifier	Month/Year
5000	A	Order 1	072020
5000	A	Order 1	082020
5000	A	Order 1	092020
5000	A	Order 1	102020
1000	A	Order 2	042020

Did some modification to your code - But stucked at this stage : 

[Aaron_Harter · Accepted solution]

Hi @JLEE038, 

Happy to hear the logic for supplier B worked!  For supplier A, I've dropped in some new logic to create entries for each date in each order, then Summarize to create the desired output format:

[JLEE038]

Hi Aaron,

I have finally manged to try out the new logic, it works ! Albeit a wee bit late, I am really grateful of your input !

First time I am exposed to the generate rows function, it's really interesting ! Sry quite raw in Alteryx.