topic Get expression value using Macro. in QlikView

Get expression value using Macro.

Wed, 25 Nov 2020 16:16:04 GMT

I have an expression that I need to reference in my Macro.

Basically, I had about 120 nested Ifs in the background color of one of my expressions, and Qlikview doesn't allow that many. So, I decided to write a macro instead. I need to reference the value of the expression in my macro, but I can only find ways to set expression properties, not reference the values themselves.

My If statements looked like:

If(Customer = 'A'

and((Label = 'Metric A' and Expression < .95)

or(Label = 'Metric B' and Expression < .96)),red(),

If(Customer = 'B'

and((Label = 'Metric B' and Expression < .99)

or(Label = 'Metric C' and Expression < .96)

or(Label = 'Metric D' and Expression < .90)),red(), .........

And I'm attempting to write my macro as:

sub MakeRed

set Customer = ActiveDocument.Fields("Customer").GetSelectedValues

If Customer.Item(i).Text = "A" Then

Set Labels = ActiveDocument.Fields("Label").GetPossibleValues

For i = 0 to Labels.Count

If Labels.Item(i).Text = 'Metric A' and //here's where I need to reference the expression// ExpressionValue < .95)......

End If

End sub

Any help with this problem or a better suggestion would be much appreciated.

Re: Get expression value using Macro.

prieper — Tue, 06 Jun 2017 14:06:28 GMT

Macros are usually not that welcomed ...

Isn't there a better solution in order to replace these nested IF's? Can you do something with flagging and/or mapping?

Can you post an example?

Peter

Re: Get expression value using Macro.

Tue, 06 Jun 2017 14:10:53 GMT

I posted an example of the nested IFs in my original question:

If(Customer = 'A'

and((Label = 'Metric A' and Expression < .95)

or(Label = 'Metric B' and Expression < .96)),red(),

If(Customer = 'B'

and((Label = 'Metric B' and Expression < .99)

or(Label = 'Metric C' and Expression < .96)

or(Label = 'Metric D' and Expression < .90)),red(), .........

If there is a better solution could you clue me in?

Re: Get expression value using Macro.

prieper — Tue, 06 Jun 2017 14:20:51 GMT

A bit more info might be helpful.

In a first approach:

You may put this into a separate table, like

LOAD * INLINE [Customer, Label, Expression

A, MetricA, .95

A, MetricB, .96

B, MetricB, .99

B, MetricC, .96

etc];

This table to be linked to the facttable (Customer, Label).

Instead of "synthetic keys", you may also create a concatenated key (AUTONUMBER(Customer&Label) AS KEY).

Backgroundcolor then might be: IF(SUM(Sales) > Expression, RED()))