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,
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.
