Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
petter
Partner - Champion III
Partner - Champion III

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
sunny_talwar

What exactly does Variable include (What String)?

petter
Partner - Champion III
Partner - Champion III

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




vijetas42
Specialist
Specialist

Hi,

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

tresesco
MVP
MVP

May be this helps:

Untitled.png

Exp:

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

settu_periasamy
Master III
Master III

May be try this

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

or

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

settu_periasamy
Master III
Master III

Capture.JPG

petter
Partner - Champion III
Partner - Champion III

2015-09-24 #2.png

Not applicable
Author

Variable contains one of fields of Field.

For example;

Field contains: SP_AAA, SP_BBB, SP_CCC

Variable contains: SP_BBB

avinashelite

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

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