Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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!
It should be like:
Avg (<item_type={'<100'}>}amount)
Hope this helps!
Try This
Avg (<item_type={"<=100"}>}amount)
-Nilesh
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
Maybe try = {"<100"}>}
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.
Works for integer fields, but unfortunately not for string fields.
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!
This helps a lot as the type is always a single letter followed by 3 digits.
Thanks a lot.