Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Not applicable

Set Analysis - Search String

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

 

 

1 Solution

Accepted Solutions
MVP
MVP

Re: Set Analysis - Search String

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 thanA 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 equalA numeric comparison is made if both operands can be interpreted numerically. The operation returns the logical value of the evaluation of the comparison.
>Greater thanA 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 equalA numeric comparison is made if both operands can be interpreted numerically. The operation returns the logical value of the evaluation of the comparison.
=EqualsA 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 toA numeric comparison is made if both operands can be interpreted numerically. The operation returns the logical value of the evaluation of the comparison.
precedesASCII less thanUnlike 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

followsASCII greater thanUnlike 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




View solution in original post

14 Replies

Re: Set Analysis - Search String

What exactly does Variable include (What String)?

MVP
MVP

Re: Set Analysis - Search String

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 thanA 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 equalA numeric comparison is made if both operands can be interpreted numerically. The operation returns the logical value of the evaluation of the comparison.
>Greater thanA 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 equalA numeric comparison is made if both operands can be interpreted numerically. The operation returns the logical value of the evaluation of the comparison.
=EqualsA 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 toA numeric comparison is made if both operands can be interpreted numerically. The operation returns the logical value of the evaluation of the comparison.
precedesASCII less thanUnlike 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

followsASCII greater thanUnlike 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




View solution in original post

vijetasharma
Valued Contributor

Re: Set Analysis - Search String

Hi,

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

MVP
MVP

Re: Set Analysis - Search String

May be this helps:

Untitled.png

Exp:

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

Re: Set Analysis - Search String

May be try this

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

or

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

Re: Set Analysis - Search String

Capture.JPG

MVP
MVP

Re: Set Analysis - Search String

2015-09-24 #2.png

Not applicable

Re: Set Analysis - Search String

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

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

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