0 Replies Latest reply: Jan 31, 2017 1:41 PM by Jeff Raguse RSS

    Select values from an unjoined table using calculated values in a chart

    Jeff Raguse

      I have struggled with this issue for a few days with no real success, so it looks like I need expert advice.

       

      My task is to pull in detailed records, summarize those records and compute subtotals in a chart.  Then based on the summarized subtotals, go out to another (unjoined) table, and select "reward" amounts.

       

      My summarized table looks like this:

       

      Dealer NameDlr #PhoneStateOwnerAppl #Appl $Approve #Approve $Purch #Purch $Funded %Prize $
      XYZ2005123-456-7890FLOwner Name111$412,206.0086$319,868.0064$238,949.0074.4%600.00

       

      There will be several rows in this table.  For each row, I would want to lookup the Purch $ and Funded % (these are master measures with somewhat involved computations), in another unjoined table of rewards.  In this made up example, since the Purch $ is between 200,000 and 250,000, and the Funded % is greater than 60% the lookup reward would be 600.

       

      Here are some of the things that I have considered.

       

      Qlik Sense has a lookup function, but this does not work in the chart environment.  I am unable to lookup these values in the load editor as I am starting with detail (loan) records and the summary (by dealer) is done at the chart level.  There are other functions that I also ruled out because they do not work at the chart level.

       

      I thought the FieldIndex (and FieldValue) functions might be the answer.  To that end, I developed a way to combine the Purch $ and Funded % together by first rounding each to appropriate levels and then adding them together.  (In this made up example the combined value would be 200000.6)  I then can work with 2 columns in my reward table: a FundRatio field and a Prize field.  My latest Reward function looks like this:

       

      if($(=$(vPurchD))<min(FundLow) or $(=$(vFundPerc))<min(RatioLow) ,0,

      round(FieldValue('Prize',

      FieldIndex('FundRatio',

      num(if($(=$(vPurchD))>max(FundLow),max(FundLow),

      if(floor($(=$(vPurchD))/50000)*50000<min(FundLow),min(FundLow),floor($(=$(vPurchD))/50000)*50000)

      +if($(=$(vFundPerc))>max(RatioLow),max(RatioLow),floor($(=$(vFundPerc))*20)/20)

      )))))

       

      I switched to using variables in the Reward function in order to try and force recalculation.  While some of this function appears to work fine, the FieldIndex function does not pull a row number as it should.  I am now wondering if the FieldIndex function is intended to work as I would want it to.  Namely, I would like it to find the matching row number, then I could use this row number in the FieldValue function to find the Prize amount.  (I forced all of the Prize amounts to be unique values by adding a very small unique number to each one in the load editor, thereby allowing the FieldValue function to work properly.)

       

      I also thought about set analysis, but I think I read that this does not work with unjoined tables (check posting "pick rating on the fly using calculated score").

       

      It seems like this would be such a simple problem - I could do this in Excel quite easily, and am starting to wonder if this is even possible in Qlik Sense.

       

      Any suggestions?