Sign up to take part
Registered users can ask their own questions, contribute to discussions, and be part of the Community!
I'm a complete beginner and trying to do a simple automation after finishing core design trainings. I have raw file that comes monthly in the format below. There are five products below and I need to multiply each product(product will be in a text format) by their corresponding month in that row. Please see my example below(attached the file for reference as well)
Product A & M only: I want to multiply(*) that row for each month by 6
Product F: I want to multiply(*) that row for the months by 12
Product C 3M(30MG) & 3M(5MG): I want to multiply(*) that row by months by 3
For example: Product A: Jan-21-5*6, Feb 21-3*6, Mar-21-6*6...soo on. And I want this to be done when new months come in as well
Welcome to the Dataiku Community
If I was tackling this data transformation, and wanted to do it in a visual prepare recipe. I might fold the data into a tall format.
And then do my math in a formula.
Then if I actually wanted the data back in Wide Format I'd likely unfold the data back to the original format.
You will also want to be careful in seting up your data source so it does not to complain when the schema of the data changes when you add an additional column.
Hope this helps. Do let the community know how you are getting on with your project.
Thank you for replying back. I will do the fold but how will my math formula look like after the fold? For example, I take Product A and I want to multiply that row for month *6? Also, I will have new months coming, would that be automated meaning pick up? Do you have another way of doing this simple task?
Once you have the data in a long format (prod, month, value), then you can use a nested if to create a new column with the product: if(prod==X,3xQuantity,if(...)).
Personally, I don't like to hardcode value, therefore another suggestion is to:
I hope this helps.