1 Reply Latest reply on Feb 10, 2019 12:16 PM by Yuriy Fal

    Comparing two rows and pulling multiple data based on that

    Ayan Halder
      Account IDAccount NameCreated DateOld StageNew StageStage Change Date
      1001X1/1/2018QualifyPOC2/1/2018
      1001X1/1/2018POCLead3/1/2018
      1001X1/1/2018LeadWon4/1/2018
      2001Y3/1/2018QualifyPOC4/1/2018

       

      My question involves the table described above. I need to compare two rows and be able to do the following:

       

      IF {Account ID(Row 2) == Account ID (Row 1)} AND Old Stage(Row 2) == New Stage (Row 1), THEN Old Stage(Row 2). Also, if the row within the same Account ID is the last one (i.e., MAX (Stage Change Date)), then it should add the New Stage of that row as well. Alongside, it should pull the corresponding stage change date. The date of the first stage (i.e., Qualify) will be the "Created Date".

       

      The final result of the column will look like:

       

      For Account ID: 1001

       

      Stage
      Date
      Qualify1/1/2018
      POC2/1/2018
      Lead3/1/2018
      Won4/1/2018

       

      How can I achieve this?