Please help Prepare simple multiplication Recipe-Automation

MARU

Hi,

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

Product	STR	Jan-21	Feb-21	Mar-21	Apr-21	May-21	Jun-21	Jul-21	Aug-21	Sep-21	Oct-21
A	15 MG	5	3	6	4	3	9	6	2
C	20MG	2	4	5	9	5	7	5	3
C 3M	30MG	4	8	6	8	4	3	4	3
C 3M	5MG	5	7	6	5	8	2	6	2
F	11MG	2	3	3	8	6	3	7	1
M	5MG	4	9	5	4	7	6	9	6

tgb417

@MARU
,

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.

https://doc.dataiku.com/dss/latest/preparation/processors/fold-columns-by-name.html?highlight=fold

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.

https://doc.dataiku.com/dss/latest/preparation/processors/unfold.html?highlight=unfold

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.

MARU

Hi Tom,

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?

Manuel

Hi,

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:

• Add a editable table with just two columns (prod, multiplier)
• Join the two tables, using the prod as key
• Create a new column with a simple formula of multiplier x quantity

I hope this helps.