1 Reply Latest reply: Jun 27, 2016 12:42 PM by Ruth Hopkins RSS

    Help with a set analysis statement that allows me to identify a max value and then pull data related to that value

    Ruth Hopkins

       

      History table (records related to a claim): 

       

      HistoryID | ClaimID | EventDate | Segment | ClaimOpen 

       

      ----------+---------+------------+---------+---------- 

       

      1 | 1 | 2016-01-01 | Level 3 | Y 

       

      2 | 1 | 2016-01-02 | Level 3 | Y 

       

      3 | 1 | 2016-01-03 | Level 1 | Y 

       

      4 | 1 | 2016-02-15 | Level 1 | N 

       

      5 | 2 | 2016-01-01 | Level 0 | Y 

       

      6 | 2 | 2016-01-01 | Level 1 | Y 

       

      7 | 2 | 2016-01-02 | Level 3 | Y 

       

        

       

      Claim table: 

       

      ClaimID | Adjuster 

       

      --------+------------ 

       

      1 | Smith, Joe 

       

      2 | Jane, Mary 

       

      The script will set an event date range, where  

       

      let vEndDate = "2016-01-31"; 

       

      I need to be able to count all ClaimIDs where the Max EventDate in the History table, relative to the date  

       

      range (vEndDate), is on a row where the Segment = Level 2, Level 3 or Level 4 and a ClaimOpen value = Y.  

       

      Since I may have duplicate EventDates, I need to interrogate the HistoryID, since the largest HistoryID value  

       

      corresponds to the most recent EventDate, per ClaimID. Once I have identified that HistoryID, I then only  

       

      want to include/count the rows that meet the above 2 

       

      conditions.  

       

      This is the expression I built: 

       

      Count({ 

       

      <EventDate={"<=$(=vEndDate)"} 

       

      ,Segment={'Level 2','Level 3','Level 4'} 

       

      ,HistoryID={"=aggr(max(HistoryID), ClaimID)"} 

       

      ,ClaimOpen={"Y"} 

       

      > 

       

      } distinct ClaimID) 

       

      For this example, I need the expression to identify rows 3 and 7 as the rows with the max history for  

       

      their respective claim IDs, but only count row 7 since that is the only max row that meets the Segment and ClaimOpen conditions.

       

        • Re: Help with a set analysis statement that allows me to identify a max value and then pull data related to that value
          Ruth Hopkins

          I think I have solved part of the problem - I have figured out how to bring back the max historyID per claimID using FirstSortedValue.  Even if the historyID's share the same date it is bringing back the max HistoryID value.  Here is the syntax and it is working:

          =Max(aggr(firstsortedvalue (HistoryID, -EventDate), ClaimID))

           

          So now what I have to do is, for the max HistoryID value, count it if the HistorySegment and HistoryClaimOpen values meet my parameters.  I am trying to use a set analysis expression to set the HistoryID to the max, but Qlik doesn't like it.  Can I do this?  And if so, how should it be wrttien?

          Count ({
          <
          HistoryID={Max(aggr(firstsortedvalue (HistoryID, -EventDate), ClaimNumber))}
          ,HistorySegment={'Level 2', 'Level 3',
          "Level 4'}
          ,HistoryClaimOpen = {'Y'}>
          }distinct ClaimID)