Pick() Match() with '>=*' condition. Is it possible?

[shyamcharan]

Hi Experts,

I am trying to create a calculated condition and my requirement is something like below:

IF(Code='1A' AND Seconds >=600, 'High','Low')

Can i get it done with Pick() match() ?

Thanks in advance.

[Anil_Babu_Samineni]

I heard instead of IF condition it would better Pick + Match combination. So here, I don't notice whether the performance increased or not. But, Can you let us know how many Dimensions(In that, How may calc. dim) and expressions you are using in single object

But, I would love this only --- IF(Code='1A' AND Seconds >=600, 'High','Low')

May be try this? I've not tested but similar one

=Pick(Match(Code,'1A',Seconds,'>=600'),'High','Low')

[Anonymous]

Hi Shyam,

Can u please try with this,

=match([code]='1A','1B','1c') and match([seconds],'600','700','800')>=600,'High','low'.

R/Murali.

[marcus_sommer]

In general numeric comparisons could be possible with a pick(match()) construct but you will need some preparing - maybe something like this:

pick(match(floor(Seconds/100), 0,1,2,3,4,5,6,7,8,9),

     'low','low','low','low','low','low','high','high', 'high', 'high')

If this kind of checking is suitable in your case where at least one additional condition to the Code field is necessary - I don't know.

- Marcus

[Peter_Cammaert]

What are you trying to accomplish? Improved performance? Shorter script code?

The logical expression is too short to be replaced by two funciton calls, and whether you use an IF() or a Pick(Match()) combo, the net result is the same: chose between two cases. Typically something for an IF() call...

Pick(Match()) are better suited for situations where you have many possible comparisons which would normally end up in a nested IF() with multiple levels.

[sunny_talwar]

marcus_sommer‌ -  Wouldn't it be better to use Ceil instead of Floor? 650 would be floored to 6 instead of 7 and would be part of low I think

[sunny_talwar]

You can try like this

Pick(Match(Seconds < 600 and Code = '1A', -1, 0), 'low', 'high') as Flag;

[marcus_sommer]

Yes, it could be that a different rounding is needed respectively fits better. Here I intended to round 599 to 500 instead to 600 to ensure that it would be considered as >= 600.

- Marcus

[shyamcharan]

Thank you all for your responses.

And my sincere apologies for the delay in my response. Was off to work for a couple of days.

Please see the attachment to this post.

I am trying to add a calculated dimension based on the conditions and then derive the measure/expression based on the calculated dimension.

In the Calculated dimension, I would like to see a combination values as a dimension as shown below:

My requirement...

Dimension          Measure(time condition)          All Measures

1a                              2                                        3

1b & 1c                      6                                         7

2i  & 2ie & r2              4                                        10

2a & 2b                       5                                        7

r3                                2                                        5

My Original data..

Table:

Code	Time	Measure
1a	10	1
1a	15	1
1a	20	1
1b	20	1
1b	30	1
1b	40	1
1b	50	1
1c	60	1
1c	70	1
1c	80	1
2i	90	1
2i	100	1
2i	110	1
2ie	120	1
2ie	130	1
2ie	140	1
r2	150	1
r2	160	1
r2	170	1
r2	180	1
2a	190	1
2a	200	1
2a	210	1
2b	220	1
2b	230	1
2b	240	1
2b	250	1
r3	260	1
r3	270	1
r3	280	1
r3	290	1
r3	300	1

Condition:

IF(Code='1a' and Time  >= 15, sum(Measure),

     IF((Code='1b' Or Code='1c') and Time  >= 30, sum(Measure),

            IF((Code='2i' Or Code='2ie' Or Code='r2') and Time  >= 150, sum(Measure),

                    IF((Code='2a' Or Code='2b') and Time  >= 210, sum(Measure),

                              IF((Code='r3') and Time  >= 290, sum(Measure))

Sorry, I should have posted in my first post.

[Anil_Babu_Samineni]

Like this? I am not sure how you are getting 4 for 2i of Measure the condition Because this may typo error / Could be logic behind. In fact, you have only 3 time which >= 15. Check and share exact output