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

Tue, 31 Jan 2017 18:41:58 GMT

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

Owner Name

111

$412,206.00

$319,868.00

$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?

topic Select values from an unjoined table using calculated values in a chart in App Development

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