4 Replies Latest reply on Sep 27, 2018 12:14 PM by Stephen Groff

    LOD Calculation Help

    Stephen Groff

      Hi All!  My brain is busting over this.  Need some LOD pros... assuming I need an LOD for this.


      See attached workbook extract (Tab 5)


      I need my data to answer these questions:


      Was the event audited?

      Was was the Event #?

      Was there a standard event(s) written for the [Trace No] after it was audited?

      What were those Event #s?



      Was the event audited?

      The main columns I need to focus on here are [Production ID #] and [Trace No]

           - It's difficult to explain but the [Trace No] is the [Production ID #], but only 1 will populate on a single row of data:


      From this I know if the Event was audited:




      Was there a standard event written for the event after it was audited?


      Remember [Trace No] and [Production ID #] are the same number.  They are both Production IDs, but for whatever reason we call one of them TRACE_NUM


      In any case, my next calculation needs to go something like this:


      IF the event was audited (T/F), and the [Trace No] from that audited event matches the [Production ID #] of a different standard event, then TRUE else FALSE END.


      I'm trying to see which audited events still had failures after they were audited.


      What were those Event #s?

      I have no idea how I'm going to repopulate different events on the same row of data in my Viz.


      Columns I need to show:


      Was Event Audited (T/F)?

      Event # of Audited Event?

      Were there Failures After the Audit?

      What were those Event #(s)?


      Any help would be greatly appreciated!

        • 1. Re: LOD Calculation Help
          Eric Valpey

          My first thought is that since you are using a custom SQL statement that you could solve for this with a left join to a subquery of distinct Production IDs all audited events.

          • 2. Re: LOD Calculation Help
            Stephen Groff

            That sounds wonderful, except I barely know SQL... putting together the SQL query I have took me an entire afternoon, and it's pretty basic.


            I just want to answer a few simple questions... beginning with... Does the Trace number match the production ID # on any row?  If it does then give me that event number(s), else null end


            I wish I knew more about fixed LODs


            I keep going to {fixed Production ID #:??? (IF production ID # = Trace No THEN 1 else 0 END)} but all that does is return zeros.

            • 3. Re: LOD Calculation Help
              Eric Valpey

              Ah, well the LOD might be:


              {fixed [Production ID #]: max(IF [Production ID #] = [Trace No] THEN 1 else 0 END)}


              You'd be using max as an aggregate over all cases of that Production ID (so, is this production ID ever equal to the Trace No?)

              • 4. Re: LOD Calculation Help
                Stephen Groff

                Here is a table representation:

                I have 2 different rows.

                -If the Production ID only shows up - its a failure  (This row of data was only created because we systematically record failure events)

                -if the Trace No shows up - it means it was audited  (This row of data was only created because we systematically created an event to show that we audited the event)


                So pretty much I can create 2 different calcs:

                1.  was this a failure event (IF not ISNULL (Production ID) then "Failure" END)

                2.  was this an audit event (IF not ISNULL (Trace No) then "Audited" END)


                I just don't know how to put both of these on the same row.

                IF Event was "Audited" then give me the Event # for the "Failure"


                I can create and join two different tables, but then I just have a list of duplicated events which takes the functionality away from the "VIEW" data feature that makes (in our facility's case) worth using.


                I think this can be done using LODs, but even though I've spent hours trying to understand them... I'm still waiting for the light bulb to turn on.