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

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

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 Name Dlr # Phone State Owner Appl # Appl \$ Approve # Approve \$ Purch # Purch \$ Funded % Prize \$ XYZ 2005 123-456-7890 FL Owner Name 111 \$412,206.00 86 \$319,868.00 64 \$238,949.00 74.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?