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?
