In monarch dps version 14, how in a calculated field retrieve the value of another field from the previous row?

JEAN_21512
JEAN_21512 Altair Community Member
edited June 2021 in Community Q&A
Tagged:

Answers

  • Baba_Majekodunmi_703
    Baba_Majekodunmi_703
    Altair Employee
    edited June 2021

    Hi Jean!

     

    How are you? It depends on what you're trying to accomplish. It's possible to do something like this with some operations like an Unpivot and a Pivot. Can you give a little more insight on what you're try to accomplish?

     

    Best Regards,

    Baba

  • Stephen_22573
    Stephen_22573
    Altair Employee
    edited June 2021

    Hello Jean,

    The previous row of any column can be derived within a single table using current functions/formulas. These are the steps:

    1. Add Calculated Field that will include values only for odd numbered rows.
    • If( Mod ( Rowno(), 2) = 0, [Account Number], "")
  • Ditto that column.
  • Add Calculated Field that will include values only for even numbered rows.
    • If( Mod ( Rowno(), 2) = 1, [Account Number], "")
  • Ditto that column.
  • Add Calculated Field that will take the value from the odd or even, depending on if the current row is even or odd. This will be your "Previous Row" field, which can be referenced in subsequent formulas/functions.
    • If( Mod ( Rowno(), 2) = 0, [Even Values], [Odd Values])
  • Delete the even column.
  • Delete the odd column.

    • By adding additional steps, you can generate columns that present the value three, four or n rows previous.

    If you need to know subsequent rows' values, you would first need to sort in reverse row number order, then follow these steps, then re-sort in the original order.

    Good Luck!

    Stephen

  • JEAN_21512
    JEAN_21512 Altair Community Member
    edited June 2021

    Bonjour Baba,

    Merci Stephen, mais je ne vois pas comment cela peut fonctionner : je réexplique mon problème en prenant l'exemple de la pièce jointe :

    quand la VALEUR de la ligne précédente = la VALEUR de la ligne en cours alors je mets mon champ RESULTAT à 1 sinon il est égal à CUMUL+1

    Bien cordialement,

     

  • JEAN_21512
    JEAN_21512 Altair Community Member
    edited June 2021

    Bonjour Stephan et Baba,

    je clos ce sujet car j'ai trouvé la solution via des rowno() et une jointure dont vous trouverez un exemple en pièce jointe.

    Merci à vous et bonne journée.

    • Categories