Referencing a value from a calculation

[UncleRiotous]

Apologies in advance for a long post, hopefully it's fairly clear. I've got a problem referencing a lookup value from a calculation. We've tried a number of things to resolve what should be pretty simple but we can't find a solution.

I have two island tables in a Qlik app (I am aware generally island tables are a bad idea). Inline loads for these are at the bottom of the post.

Using the data in MAIN_DATA I can create the following table in Qlik.

The expression for the final column is...

=Aggr(Sum(NUM_VAL)/Sum(DEN_VAL),CUSTOMER,WE_DATE,PRODUCT)

I want to check the value returned against the LOOKUP_VALUE field for a given customer and return the LOOKUP_RESULT. So for the top line of the example I want to look for 100 in the LOOKUP_VALUE where CUSTOMER_NAME is Alice and return whatever is in LOOKUP_RESULT. I would have expected this to work using the following syntax as a Dimension...

=If(CUSTOMER_NAME = CUSTOMER And LOOKUP_VALUE = Aggr(Sum(NUM_VAL)/Sum(DEN_VAL),CUSTOMER,WE_DATE,PRODUCT),LOOKUP_RESULT)

This just returns blanks though. We've tried a variety of things (including joining the datasets on CUSTOMER) but we can't find a way to do this.

The code below just builds the data structure in case someone wants to try it for themselves.

MAIN_DATA:

Load

CUSTOMER,

    Date(WE_DATE) AS WE_DATE,

    NUM_VAL,

    DEN_VAL,

    PRODUCT

Inline [

CUSTOMER,WE_DATE,NUM_VAL,DEN_VAL,PRODUCT

John,03/05/2024,200,4,A

John,03/05/2024,100,2,B

John,03/05/2024,50,2,C

John,10/05/2024,75,3,A

John,10/05/2024,400,4,B

John,10/05/2024,75,1,C

John,17/05/2024,66,66,A

John,17/05/2024,200,4,B

John,17/05/2024,100,2,C

Bob,03/05/2024,50,2,A

Bob,03/05/2024,75,3,B

Bob,03/05/2024,400,4,C

Bob,10/05/2024,75,1,A

Bob,10/05/2024,66,66,B

Bob,10/05/2024,200,4,C

Bob,17/05/2024,100,2,A

Bob,17/05/2024,50,2,B

Bob,17/05/2024,75,3,C

Alice,03/05/2024,400,4,A

Alice,03/05/2024,75,1,B

Alice,03/05/2024,66,66,C

Alice,10/05/2024,200,4,A

Alice,10/05/2024,100,2,B

Alice,10/05/2024,50,2,C

Alice,17/05/2024,75,3,A

Alice,17/05/2024,400,4,B

Alice,17/05/2024,75,1,C

]

;

LOOKUP_DATA:

Load

CUSTOMER_NAME,

    LOOKUP_VALUE,

    LOOKUP_RESULT

Inline [

CUSTOMER_NAME,LOOKUP_VALUE,LOOKUP_RESULT

John,1,20

John,25,11

John,50,27

John,75,18

John,100,-7

Bob,1,30

Bob,25,8

Bob,50,-9

Bob,75,18

Bob,100,-3

Alice,1,0

Alice,25,-5

Alice,50,-29

Alice,75,27

Alice,100,-19

]

;

[rodrigo_martins]

The best way, in my opinion, would be to resolve this in the script. You can use the CUSTOMER and LOOKUP_VALUE fields to generate a key (CUSTOMER & '|' & LOOKUP_VALUE), and the LOOKUP_RESULT as a value to create a mapping table.

Regarding the expression, if you have several other dimensions (which would justify the use of Aggr), it is also possible to reproduce this aggregation via script, with a temporary table reading only the relevant columns from the MAIN_TABLE and applying a group by. Then use the ApplyMap function to find the LOOKUP_RESULT, as the final table will contain both the fields used to generate the mapping key: the CUSTOMER_NAME fields (which is equivalent to the CUSTOMER field, as far as I understand) and the result of the expression, which matches the LOOKUP_VALUE.

The final step would be a join with the original table, using a key composed of all the aggregation fields (or even another applymap using this resulting table as a base).

If the LOOKUP_VALUE values ​​are not exactly the same as those in the expression, but rather a range, you can try to resolve it with an INTERVAL MATCH, which allows joins between tables with data that are related in this way.

Hope this helps.

[UncleRiotous]

Rodrigo, thanks for the answer.

The data I shared is a simplified version of the problem. I can't do this in the load script as I need to filter the results in various ways to return the aggregated expression and then look that up. So depending on filtering we will have different results coming back but we still need to lookup the current answer against the lookup value and return the correct answer.

The full dataset would have 100 results (one for each percentage) for each customer. Depending on filtering sometimes we'd return 23% sometimes 76% but we'd want to covert that correctly each time so we really need a dynamic lookup table.

[MeehyeOh · Accepted solution]

Hi, @UncleRiotous 

Refer to my test results.

1) Load Script : I created a relationship between the two tables and created a new field.

(Never mind changing the date format of the "WE_DATE" field.)

MAIN_DATA:

Load

CUSTOMER,

    Date(Date#(WE_DATE,'DD/MM/YYYY'),'YYYYMMDD') AS WE_DATE,

    NUM_VAL,

    DEN_VAL,

    PRODUCT

Inline [

CUSTOMER,WE_DATE,NUM_VAL,DEN_VAL,PRODUCT

John,03/05/2024,200,4,A

John,03/05/2024,100,2,B

John,03/05/2024,50,2,C

John,10/05/2024,75,3,A

John,10/05/2024,400,4,B

John,10/05/2024,75,1,C

John,17/05/2024,66,66,A

John,17/05/2024,200,4,B

John,17/05/2024,100,2,C

Bob,03/05/2024,50,2,A

Bob,03/05/2024,75,3,B

Bob,03/05/2024,400,4,C

Bob,10/05/2024,75,1,A

Bob,10/05/2024,66,66,B

Bob,10/05/2024,200,4,C

Bob,17/05/2024,100,2,A

Bob,17/05/2024,50,2,B

Bob,17/05/2024,75,3,C

Alice,03/05/2024,400,4,A

Alice,03/05/2024,75,1,B

Alice,03/05/2024,66,66,C

Alice,10/05/2024,200,4,A

Alice,10/05/2024,100,2,B

Alice,10/05/2024,50,2,C

Alice,17/05/2024,75,3,A

Alice,17/05/2024,400,4,B

Alice,17/05/2024,75,1,C

]

;

LOOKUP_DATA:

Load

CUSTOMER_NAME As CUSTOMER,

    CUSTOMER_NAME&LOOKUP_VALUE As NameValue,

    LOOKUP_VALUE,

    LOOKUP_RESULT

Inline [

CUSTOMER_NAME,LOOKUP_VALUE,LOOKUP_RESULT

John,1,20

John,25,11

John,50,27

John,75,18

John,100,-7

Bob,1,30

Bob,25,8

Bob,50,-9

Bob,75,18

Bob,100,-3

Alice,1,0.1

Alice,25,-5

Alice,50,-29

Alice,75,27

Alice,100,-19

]

;

2) Visualization

Chart : Table 

Dimension1 : WE_DATE

Dimension2 : CUSTOMER

Dimension3 : PRODUCT

Dimension4: Keepchar(NameValue,'0123456789') => This is the same value as "LOOK_VALUE".

Measure1 : If(NameValue = MaxString(CUSTOMER)&Aggr(Sum(NUM_VAL)/Sum(DEN_VAL),CUSTOMER,WE_DATE,PRODUCT,NameValue),Sum(LOOKUP_RESULT))

[UncleRiotous]

Thanks MeehyeOh. I'll have a play and see if I can get that to work.

[UncleRiotous]

That works like a dream. Thank you so much.