Please help Prepare simple multiplication Recipe-Automation

Options
MARU
MARU Registered Posts: 9 ✭✭✭

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

ProductSTRJan-21Feb-21Mar-21Apr-21May-21Jun-21Jul-21Aug-21Sep-21Oct-21
A15 MG53643962
C20MG24595753
C 3M30MG48684343
C 3M5MG57658262
F11MG23386371
M5MG49547696

Answers

  • tgb417
    tgb417 Dataiku DSS Core Designer, Dataiku DSS & SQL, Dataiku DSS ML Practitioner, Dataiku DSS Core Concepts, Neuron 2020, Neuron, Registered, Dataiku Frontrunner Awards 2021 Finalist, Neuron 2021, Neuron 2022, Frontrunner 2022 Finalist, Frontrunner 2022 Winner, Dataiku Frontrunner Awards 2021 Participant, Frontrunner 2022 Participant, Neuron 2023 Posts: 1,595 Neuron
    Options

    @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
    MARU Registered Posts: 9 ✭✭✭
    Options

    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
    Manuel Alpha Tester, Dataiker Alumni, Dataiku DSS Core Designer, Dataiku DSS ML Practitioner, Dataiku DSS Core Concepts, Dataiku DSS Adv Designer, Registered Posts: 193 ✭✭✭✭✭✭✭
    Options

    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.

Setup Info
    Tags
      Help me…