Replace value based on the previous/next value in the column

Options
Konrad
Konrad Dataiku DSS Core Designer, Registered Posts: 2 ✭✭✭

Hi,

I have a fixed price of the current quarter. Then I have the percentage difference based on the other input (raw to raw). It looks like this more or less:

IDITEMPRICEDATEDIFF %
1A3000

01.01.2020

-0.01
2A YYYYYYY01.04.2020 -0.05
3A XXXXXX01.07.2020
4B2400

01.01.2020

0.04
5B ZZZZZZ01.04.2020 -0.09
6B VVVVVV01.07.2020

I want to get the YYYYY value by subtracting Price of ID 1 by percentage DIff of ID 1 ( YYYY then wound be 2970).

Then to get XXXXX value I want to do the same but with price YYYYYY and so on.


Operating system used: Win 10

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

    @Konrad

    Welcome to the Dataiku community.

    If I were trying to do something like this with visual recipes and not code, I’d be taking a look at the window recipe to get data from prior rows onto the current row. Once I had my lag values on the current row, I’d likely use a visual prepare recipe to add a formula step to do the calculations.

    These Dataiku academy course covers these two recipes in these two courses.

    The basic ideas are covered in this course:

    https://academy.dataiku.com/visual-recipes-overview-1

    More details are provided here with some hands on exercises.

    https://academy.dataiku.com/visual-recipes-102

    And here is something more on formulas

    https://academy.dataiku.com/basics-102/497953

    Hope this helps please let us know how you are getting on with your project.

  • Konrad
    Konrad Dataiku DSS Core Designer, Registered Posts: 2 ✭✭✭
    Options

    Thanks Tom.

    What you described I did to get those month-2-month percentage difference since this is based on other column input with all values filled in.

    Now I need to get the same for one fixed value only where all other are blank. Move that value to the next raw and same again.

    My data is more than 50 rows with dates per each item.

    I thought maybe there is a formula I could use to achieve it?

Setup Info
    Tags
      Help me…