Announcements
cancel
Showing results for
Did you mean:
Creator III

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

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() ?

1 Solution

Accepted Solutions
Creator III
Author

Hi Anil,

I think i found it.

I used the logic:

=Count({<Code={'1a'},Time={'>=15'}>+

<Code={'1b','1c'},Time={'>=30'}>+

<Code={'2I','2IE','r2'},Time={'>=150'}>+

<Code={'2a','2b'},Time={'>=210'}>+

<Code={'r3'},Time={'>=290'}>}Measure)

14 Replies

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')

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
Anonymous
Not applicable

Hi Shyam,

Can u please try with this,

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

R/Murali.

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

Partner - Champion III

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.

MVP

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

MVP

You can try like this

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

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

Creator III
Author

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.

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

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
Community Browser