topic Re: Calculation condition possible values, multiple values in App Development

Calculation condition possible values, multiple values

enfodavid — Sat, 16 Nov 2024 04:58:15 GMT

I want to be able to easily add and subtract fields from possible values in a calculation condition. If there is any other combination I don't want it to be true. In the example below I get it to work with two values; Value1 and Value2.

if(concat(distinct [Field1], ', ') = 'Value1' or
concat(distinct[Field1], ', ') ='Value2' or
concat(distinct[Field1], ', ') ='Value1, Value2',1,0)

I want to be able to do it easily for more than 2 values. For each value added the code would get longer, the next example is with a third added value;

if(concat(distinct [Field1], ', ') = 'Value1' or
concat(distinct[Field1], ', ') ='Value2' or
concat(distinct[Field1], ', ') ='Value3' or
concat(distinct[Field1], ', ') ='Value1, Value2' or
concat(distinct[Field1], ', ') ='Value1, Value3' or
concat(distinct[Field1], ', ') ='Value2, Value3' or
concat(distinct[Field1], ', ') ='Value1, Value2, Value3',1,0)

So the code will get exponentially longer and longer, even though I just want another field value added. Has anyone done anything similar without the hassle of longer and longer code?

Re: Calculation condition possible values, multiple values

sunny_talwar — Wed, 14 Aug 2019 11:57:07 GMT

How about this?

If( SubStringCount(Concat(DISTINCT Field1, ', '), 'Value1') = 1 or SubStringCount(Concat(DISTINCT Field1, ', '), 'Value2') = 1 or SubStringCount(Concat(DISTINCT Field1, ', '), 'Value3') = 1 , 1, 0)

Re: Calculation condition possible values, multiple values

sunny_talwar — Wed, 14 Aug 2019 11:58:38 GMT

You can also do this with WildMatch or Like function.

If( WildMatch(Concat(DISTINCT Field1, ', '), '*Value1*', '*Value2*', '*Value3*') , 1, 0)

Re: Calculation condition possible values, multiple values

enfodavid — Wed, 14 Aug 2019 12:08:18 GMT

This would mean that if there are other possible values then it will still get calculated. For example if it's possible to choose Value1 and Value5

Re: Calculation condition possible values, multiple values

sunny_talwar — Wed, 14 Aug 2019 12:15:03 GMT

I guess so... but isn't that what you original expression will do to?

Re: Calculation condition possible values, multiple values

enfodavid — Wed, 14 Aug 2019 12:16:16 GMT

No, it will exclude everything that isn't exactly just 1, just 2 or just 3 or any combination of the three

Re: Calculation condition possible values, multiple values

sunny_talwar — Wed, 14 Aug 2019 12:18:01 GMT

Right, that's true. I got confused. Let me think about it

Re: Calculation condition possible values, multiple values

sunny_talwar — Wed, 14 Aug 2019 12:41:38 GMT

This is still not perfect, but see if this looks any better

=If(GetSelectedCount(Field1) = 1, If(Match(Concat(DISTINCT Field1, ', '), 'Value1', 'Value2', 'Value3'), 1, 0), If(GetSelectedCount(Field1) = 2, If(Match(Concat(DISTINCT Field1, ', '), 'Value1, Value2', 'Value1, Value3', 'Value2, Value3'), 1, 0), If(GetSelectedCount(Field1) = 3, If(Match(Concat(DISTINCT Field1, ', '), 'Value1, Value2, Value3'), 1, 0), 0)))

Re: Calculation condition possible values, multiple values

enfodavid — Wed, 14 Aug 2019 12:45:34 GMT

It gets the same job done, but the underlying problem remains. If I would add a Value4 it would get larger, and then a Value5, even larger than that. Thanks for the help and your time but the problem remains

Re: Calculation condition possible values, multiple values

sunny_talwar — Wed, 14 Aug 2019 12:50:43 GMT

I agree... the only way to make this easy is to create a new field in the script

Table: LOAD *, If(Match(Field1, 'Value1', 'Value2', 'Value3'), 1, 0) as Flag; LOAD * INLINE [ Field1 Value1 Value2 Value3 Value4 Value5 ];

And use this expression

=Min(Flag)

This should work just like your actual expression

Re: Calculation condition possible values, multiple values

enfodavid — Wed, 14 Aug 2019 12:59:24 GMT

This would have the same built in mistake where if something has Value5 as well as Value1 it would get a Flag

Re: Calculation condition possible values, multiple values

sunny_talwar — Wed, 14 Aug 2019 13:11:20 GMT

How? Value1 will have Flag 1, and Value5 will have Flag 0... and Min(Flag) = 0?

Re: Calculation condition possible values, multiple values

Vegar — Wed, 14 Aug 2019 13:19:02 GMT

What if you do a comparison like the one I've done below? In my quick test it seems to work as desired.

if( Concat(DISTINCT Field1, ', ') = Concat({< Field1 *= {'Value1', 'Value2'}>}DISTINCT Field1, ', ') ,1,0)

I've attached my test qvw app

Re: Calculation condition possible values, multiple values

enfodavid — Wed, 14 Aug 2019 13:21:41 GMT

Oh, I didn't see the last part. That might actually be it!

Re: Calculation condition possible values, multiple values

enfodavid — Wed, 14 Aug 2019 13:32:03 GMT

Thank you for your time and help!