2 Replies Latest reply on Oct 26, 2016 3:37 AM by Hima vardhan Reddy Pavuluri

    How to put IF condition across multiple rows

    saijyothi bandagi

      Please see attached packaged workbook

       

      I just need a calculated field called "Valid Order" with logic as follows:

       

      If a particular order ID has "Tag" as "Status_Change" and "Value Before"=initiated and "Value after"=verified, then its a valid order

      otherwise the order is invalid

       

      please note that the same Order ID is coming in multiple rows.  I think the FIXED function can be used but I am unable to apply it correctly.

        • 1. Re: How to put IF condition across multiple rows
          Simon Runc

          Hi Saijyothi,

           

          So yes LoD is one way to go and is probably the most straight forward. It also has the advantage of creating a "real" valid/invalid dimension (so an OrderID is tagged Valid or Invalid for every row that it appears). This also means it's VizLoD independant...so you could easily have a COUNTD, say, of Valid/Invalid, without requiring the OrderID to be in the VizLoD.

           

          On the tag example, I've created the calculation in 2 stages so you can see what's happening.

          First I create a Row Level calc which only populates the row if [Tag1]="STATUS_CHANGE", else it's NULL

          [Tag - Only if Status Change]

          IIF([Tag1]="STATUS_CHANGE",[Tag1],NULL)

           

          I can then create a FIXED LoD, bring back the MAX (or MIN actually would also work) of all entries by OrderID. As the MAX (or MIN) of something (even text) and NULL is always the something! So this looks like

          [Tag Status Change All Rows - LoD]

          {FIXED [Order id]: MAX([Tag - Only if Status Change])}

           

          I then follow a similar route for the other, where I've nested the Row Level check in the LoD calc

          {FIXED [Order id]: MAX(IIF([Value after]="VERIFIED",[Value after],NULL))}

           

          and

           

          {FIXED [Order id]: MAX(IIF([Value Before]="INITIATED",[Value Before],NULL))}

           

          Once we have all these we can create our final dimension

          [Vallid Order]

          [Tag Status Change All Rows - LoD]="STATUS_CHANGE"

          AND [Value Before] = "INITIATED"

          AND [Value after] = "VERIFIED"

           

          Hope that makes sense....let me know if not

          • 2. Re: How to put IF condition across multiple rows
            Hima vardhan Reddy Pavuluri

            Hi , Try this Calculation :

             

            CASE  { FIXED [Order id] : MAX(IF [Tag]='Status_Change' AND [Value Before]='initiated' AND [Value after]='verified' THEN 1 else 0 END )}

            WHEN 1 THEN 'Valid Order'

            ELSE 'Invalid'

            END