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,








      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?