topic Interpreting Operator from Excel-file in QlikView

Interpreting Operator from Excel-file

Tue, 27 Sep 2016 07:32:48 GMT

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,

)

) 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

Re: Interpreting Operator from Excel-file

MarcoWedel — Tue, 27 Sep 2016 23:56:47 GMT

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

Re: Interpreting Operator from Excel-file

Wed, 28 Sep 2016 04:47:40 GMT

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