topic Re: Set modifier field greater than field and variable in QlikView

Set modifier field greater than field and variable

Anonymous — Tue, 06 Mar 2018 11:52:12 GMT

Hello,

I'm trying to compare a field on one part to a field and a variable on another part, inside a set modifier.

Business Rule: Cap<= (Rate + vShock)

The Cap and Rate are fields and the vShock is a variable.

I have tried the following syntaxes:

sum ({<Cap={"<=$(=Rate + $(vShock))"}>} Balance)

sum ({<Cap={"<=($(=Rate) + $(vShock))"}>} Balance)

sum ({<Cap={"<=$(=Rate + vShock)"}>} Balance)

[...]

I know how to use a set modifier for a variable alone, and for a field alone (dollar expansion) but I'm struggling with merging them.

Any thoughts?

Thanks

Re: Set modifier field greater than field and variable

lironbaram — Tue, 06 Mar 2018 11:57:41 GMT

what exactly is the problem , you get unexpected results

or you get no results at all?

Re: Set modifier field greater than field and variable

Anonymous — Tue, 06 Mar 2018 12:09:51 GMT

Rather no results

Re: Set modifier field greater than field and variable

Chanty4u — Tue, 06 Mar 2018 12:13:02 GMT

try this

sum ({$<Cap={'<=$(=Rate + $(=vShock))'}>} Balance)

sum ({$<Cap={'<=($(=Rate) + $(=vShock))'}>} Balance)

sum ({$<Cap={'<=$(=Rate + $(=vShock)'}>} Balance)

Re: Set modifier field greater than field and variable

sunny_talwar — Tue, 06 Mar 2018 12:15:04 GMT

What dimension are you trying to evaluate this condition on?

Cap<= (Rate + vShock)

For example, let's say you have a field called Stock and you only want to find the Sum of balance where the above condition is true for a stock... then you can try this

Sum({<Stock = {"=Cap <= Rate + $(vShock)"}>} Balance)

Re: Set modifier field greater than field and variable

jonathandienst — Tue, 06 Mar 2018 12:19:51 GMT

sum ({<Cap={"<=$(=Rate + $(vShock))"}>} Balance)

You can't have a naked field* on the RHS of a set expression. As the expression is calculated before the table is built, [Rate] will evaluate to null at this point since there is more than one possible value.

You can do this with a Sum(If()) statement - although this may perform poorly if your data set is not small. You can set a flag in the load script - although if vShock is intended to be dynamic, then the load script is not really an option. Finally, if there is some identifying field in the model that ties the Cap and Rate fields, you could use that in your set expression. Edit - this last is the option Sunny has proposed.

A sum(if()) implementation would be

Sum(If(Cap <= Rate + $(vShock), Balance))

or even simpler (if vShock is a number):

Sum(If(Cap <= Rate + vShock, Balance))

*A naked field reference is a reference to a field outside of an aggregation function (like Sum(), Avg(), Max()).

Re: Set modifier field greater than field and variable

Anonymous — Tue, 06 Mar 2018 12:34:19 GMT

Didnt Work. Will upload a QVW

Re: Set modifier field greater than field and variable

Anonymous — Tue, 06 Mar 2018 12:34:58 GMT

The condition is on the CAP field. Will upload a QVW

Re: Set modifier field greater than field and variable

sunny_talwar — Tue, 06 Mar 2018 12:37:28 GMT

A single cap will ever have one and only one Rate?

Re: Set modifier field greater than field and variable

Anonymous — Tue, 06 Mar 2018 12:42:41 GMT

Sorry to ask this but how can i attach a qvw to send you?

Meanwhile, here is an example:

[

Deal, Cap, Rate, Balance

1, 5, 4, 100

2, 5, 2, 200

];

Shock: 2

For Deal 1, 5 <= 4 +2 ...True

For Deal 2, ... False

The result should be: 100

Re: Set modifier field greater than field and variable

Anonymous — Tue, 06 Mar 2018 12:43:01 GMT

Sorry to ask this but how can i attach a qvw to send you?

Re: Set modifier field greater than field and variable

sunny_talwar — Tue, 06 Mar 2018 12:43:44 GMT

Check this

Uploading a Sample

Re: Set modifier field greater than field and variable

sunny_talwar — Tue, 06 Mar 2018 12:44:27 GMT

Try this

Sum({<Deal = {"=Cap <= Rate + $(vShock)"}>} Balance)

Re: Set modifier field greater than field and variable

Anonymous — Tue, 06 Mar 2018 12:48:08 GMT

1)I'm actually changing from SUM(IF()) to set modifier to gain performance.

2)vShock is dynamic yes.

3) Cap and Rate are linked and in the same table. Rate is the interest rate on an account. Cap is the maximum interest rate on that same account

4) Although it seems very interesting, I'm not sure I fully understand the naked field concept. Is the following expression (that works) a naked field example: sum({<fiedl1={$(=field2)}>}balance)

Thank you

Re: Set modifier field greater than field and variable

Anonymous — Tue, 06 Mar 2018 12:49:31 GMT

I don't know why but I can only see the Mention and App features. The Attach is not visible.

Re: Set modifier field greater than field and variable

sunny_talwar — Tue, 06 Mar 2018 12:51:47 GMT

Check the attached image and qvw

Re: Set modifier field greater than field and variable

Anonymous — Tue, 06 Mar 2018 12:57:20 GMT

Thank you Sunny it worked! Can you please explain to me what you had to put Deal?

Re: Set modifier field greater than field and variable

sunny_talwar — Tue, 06 Mar 2018 12:58:38 GMT

I can, but I think HIC did a great job of explaining this here

Re: Why i am not getting same result with Above function ?

Re: Set modifier field greater than field and variable

Anonymous — Tue, 06 Mar 2018 13:02:53 GMT

No man like HIC.

Again many thanks!

Bryan

Re: Set modifier field greater than field and variable

Anonymous — Wed, 07 Mar 2018 09:19:25 GMT

Hi Sunny,

Maybe you can help me with the following. This is a continuation of yesterday's discussion.

I have 3 scenarios. 2 of them worked with the syntax you gave me

Scenario 1: Rate+Shock > Cap

Scenario 2: Rate+Shock < Floor

How do you solve it with 2 conditions

Scenario 3: Floor<Rate+Shock and Rate+Shock<Cap

I tried the below syntaxes:

Sum({<Deal = {"=Floor<(Rate + $(vShock)) Cap>(Rate + $(vShock))"}>} Balance)

Sum({<Deal = {"=Floor < Rate + $(vShock) < Cap"}>} Balance)

Finally,

Sum({<Deal = {"=Floor < Rate + $(vShock)"}, Deal = {"=Cap > Rate + $(vShock)"}>} Balance).

This last one returned a result but not the one i want as i need the set to be evaluated on the same row. A rate has one cap and one floor only

Many Thanks