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 |
Solved! Go to Solution.
Hi @JLEE038,
You may want to Filter Suppliers A & B into separate streams, then parse to isolate your data points and perform your calculations:
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 :
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:
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.