Interpreting Operator from Excel-file

[#1]

Good morning everyone

I have a client who is working with an Excel file and has three targets (green, yellow, red) per KPI. To keep it flexbile I created a column for each operator and target and import them as field into Qlikview... Example

KPI	Value	OP_Target_red	Target_red	OP_Target_yellow	Target_yellow	OP_Target_green	Target_green
1	0	>=	-0.01	>=	-0.02	<=	-0.03
2	0.136	>=	0.11	>=	0.12	>=	0.13
3	1	>	1	>	0	=	0
4	3	<=	3	<=	4	>=	5

Now I want to write in the Script the following:

if(Value&OP_Target_red&Target_red,1,

     if(Value&OP_Target_yellow&Target_yellow,2,

          3

)

)

) as [Target_Color]

My goal is a table chart with "trafficlight expression" where i can enter [Target_Color] and the result is the correct color for each KPI.

But for some reason Qlikview is not recognizing the [OP_Targed_color] as operator...

Thanks for your help!

I hope you understand what I mean

Thanks in advance and regards

Benjamin

[MarcoWedel · Accepted solution]

Hi,

maybe one solution could be:

LOAD KPI,
    Value,
    OP_Target_red,
    Target_red,
    OP_Target_yellow,
    Target_yellow,
    OP_Target_green,
    Target_green,
    If(Evaluate(Value&OP_Target_red&Target_red),
        1,
        If(Evaluate(Value&OP_Target_yellow&Target_yellow),
          2,
          3
          )
      ) as [Target_Color]
FROM [https://community.qlik.com/thread/234273] (html, codepage is 1252, embedded labels, table is @1);

hope this helps

regards

Marco

[#3]

Vielen lieben Dank!

This was almost exactly what we tried in the first instance but we used evaluate only for the operator field

Now it works perfectly fine!

Thanks and regards from Zurich

Benny