topic Re: Pick() Match() with '>=*' condition. Is it possible? in QlikView

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

shyamcharan — Mon, 20 Mar 2017 06:54:50 GMT

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.

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

Anil_Babu_Samineni — Mon, 20 Mar 2017 07:15:39 GMT

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

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

Anonymous — Mon, 20 Mar 2017 07:23:11 GMT

Hi Shyam,

Can u please try with this,

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

R/Murali.

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

marcus_sommer — Mon, 20 Mar 2017 08:52:43 GMT

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

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

Peter_Cammaert — Mon, 20 Mar 2017 09:01:59 GMT

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.

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

sunny_talwar — Mon, 20 Mar 2017 09:25:41 GMT

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

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

sunny_talwar — Mon, 20 Mar 2017 09:28:17 GMT

You can try like this

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

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

marcus_sommer — Mon, 20 Mar 2017 10:14:24 GMT

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

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

shyamcharan — Fri, 24 Mar 2017 03:33:55 GMT

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.

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

Anil_Babu_Samineni — Fri, 24 Mar 2017 04:08:30 GMT

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

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

shyamcharan — Fri, 24 Mar 2017 04:15:47 GMT

Hi Anil,

Sorry I have now corrected my earlier post.

It should be 2i & 2ie & r2 NOT just 2i. Hope this clears confusion.

Also, I have reviewed and the condition is correct.

sorry again for the confusion.

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

Anil_Babu_Samineni — Fri, 24 Mar 2017 04:22:17 GMT

Till Approx how many codes you have in real data? I am expecting number

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

shyamcharan — Fri, 24 Mar 2017 04:23:51 GMT

I have around 40 codes, but what all I want is just the below codes and the below output out of all the codes and data:

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

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

shyamcharan — Fri, 24 Mar 2017 04:59:28 GMT

Hi Anil,

I think i found it.

Please review the attached qvw and let me know your thoughts..

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)

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

Anil_Babu_Samineni — Fri, 24 Mar 2017 06:15:25 GMT

If you feel those values are good, Then it's good. Close this thread by flag Correct Answer of your reply.