topic Re: Set Analysis - Search String in QlikView

Set Analysis - Search String

Thu, 24 Sep 2015 06:27:09 GMT

Hi all,

Is it possible to do a greater than / less than on a string in set analysis, like

SUM({$<Field= {“<= $(Variable)”}>} MeasureField)

where $(Variable) contains a string value?

If not, what should be a good work around for this?

Regards,

Björn

Re: Set Analysis - Search String

sunny_talwar — Thu, 24 Sep 2015 06:29:11 GMT

What exactly does Variable include (What String)?

Re: Set Analysis - Search String

petter — Thu, 24 Sep 2015 06:46:21 GMT

There is two special operators called precedes and follows that could be used in your case I think:

SUM({$<UniqueID= {“=FieldX precedes '$(Variable)'”}>} MeasureField)

Here is the documentation of precedes and follows from the manual:

Relational Operators

All relational operators compare the values of the operands and return true (-1) or false (0) as the result. All relational operators are binary.

<	Less than	A numeric comparison is made if both operands can be interpreted numerically. The operation returns the logical value of the evaluation of the comparison.
<=	Less than or equal	A numeric comparison is made if both operands can be interpreted numerically. The operation returns the logical value of the evaluation of the comparison.
>	Greater than	A numeric comparison is made if both operands can be interpreted numerically. The operation returns the logical value of the evaluation of the comparison.
>=	Greater than or equal	A numeric comparison is made if both operands can be interpreted numerically. The operation returns the logical value of the evaluation of the comparison.
=	Equals	A numeric comparison is made if both operands can be interpreted numerically. The operation returns the logical value of the evaluation of the comparison.
<>	Not equivalent to	A numeric comparison is made if both operands can be interpreted numerically. The operation returns the logical value of the evaluation of the comparison.
precedes	ASCII less than	Unlike the <operator no attempt is made to make a numeric interpretation of the argument values before the comparison. The operation returns true if the value to the left of the operator has a text representation which, in ASCII comparison, comes before the text representation of the value on the right. Example: ' 11' precedes ' 2' returns true compare this to: ' 11' < ' 2' returns false
follows	ASCII greater than	Unlike the >operator no attempt is made to make a numeric interpretation of the argument values before the comparison. The operation returns true if the value to the left of the operator has a text representation which, in ASCII comparison, comes after the text representation of the value on the right. Example: ' 23' follows ' 111' returns true compare this to: ' 23' > ' 111' returns false

Re: Set Analysis - Search String

vijetas42 — Thu, 24 Sep 2015 06:48:46 GMT

Hi,

Can you specify exact requirement of your's.or better for string matching you can use WildMatch().

Re: Set Analysis - Search String

tresB — Thu, 24 Sep 2015 06:50:06 GMT

May be this helps:

Exp:

Concat({<String={ "=String>'BBB' "}>} String, '-')

Re: Set Analysis - Search String

settu_periasamy — Thu, 24 Sep 2015 06:59:20 GMT

May be try this

SUM(if( Field<=Variable, MeasureField))

SUM(if( Field<=$(Variable), MeasureField))

Re: Set Analysis - Search String

settu_periasamy — Thu, 24 Sep 2015 07:00:40 GMT

Re: Set Analysis - Search String

petter — Thu, 24 Sep 2015 07:03:18 GMT

Re: Set Analysis - Search String

Thu, 24 Sep 2015 12:08:58 GMT

Variable contains one of fields of Field.

For example;

Field contains: SP_AAA, SP_BBB, SP_CCC

Variable contains: SP_BBB

Re: Set Analysis - Search String

avinashelite — Thu, 24 Sep 2015 12:28:24 GMT

IF you have greater than and lesser than parameters than you can try like this :

SUM({$<Field= {“>=$(Variable2)<= $(Variable)”}>} MeasureField)

Re: Set Analysis - Search String

avinashelite — Thu, 24 Sep 2015 16:09:08 GMT

IF you have greater than and lesser than parameters than you can try like this :

SUM({$<Field= {“>=$(Variable2)<= $(Variable)”}>} MeasureField)

Re: Set Analysis - Search String

Fri, 25 Sep 2015 05:52:27 GMT

The point is that the greater than / lesser than functionality in set analysis won't work when the Variable contains a string value.

Re: Set Analysis - Search String

Fri, 25 Sep 2015 05:54:00 GMT

Good workable solution without using Set Analysis, thanks!

Re: Set Analysis - Search String

avinashelite — Fri, 25 Sep 2015 05:55:23 GMT

if you have got the answer please mark the correct answer and close this thread

Re: Set Analysis - Search String

Fri, 25 Sep 2015 05:59:43 GMT

Thanks, I did not know the precedes / follows operators and the way to use them in Set Analysis. I tried the following and it worked:

SUM({$<Field= {“Field precedes '$(Variable)'”}>} MeasureField)

I actually used the same field for both Field.