Field value less or equal a hard coded value

[#1]

Hello.

Here is my first question ...

I have an Expression to calculate average value of a field conditionned by another Field (string type).

Avg (<item_type={'T100'}>}amount)

Now, I want to change 'Equal to T100' With 'Less than T100' but cannot figure it out.

Can I please get a Clue?

[jerem1234]

It should be like:

Avg (<item_type={'<100'}>}amount)

Hope this helps!

[nilesh_gangurde]

Try This

Avg (<item_type={"<=100"}>}amount)

-Nilesh

[#4]

OK. I simplified my example a little bit too much. My item_type is indeed a string. Let say I want the types less than "Q100".

I tried Avg(<item_type={'<Q100'}>}amount) but it doesn't work.

ORIGINAL POST EDITED

[#5]

Maybe try = {"<100"}>}

[Anonymous]

if there is a format for your string item_type, then may be subfield function can help to strip out the alphabets and compare just the number part of your item_type.

[#7]

Works for integer fields, but unfortunately not for string fields.

[jerem1234 · Accepted solution]

Since it's a string, there's no number associated with it. So you must make it either by using a dual when you first load it in like:

dual(item_type, number)

Ways to get that number depends on how your data looks, if it always starts with one letter, then numbers you can try:

mid(item_type, 2)

Then you can load your field as with dual like:

dual(item_type, mid(item_type, 2))

If you don't want to touch loadscript, this should do it:

Avg({<item_type={'=mid(item_type, 2)<100'}>}amount)

Again, depends on how your item_type data varies in format.

Hope this helps!

[#9]

This helps a lot as the type is always a single letter followed by 3 digits.

Thanks a lot.