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

Field value less or equal a hard coded value

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?

1 Solution

Accepted Solutions
jerem1234
Specialist II
Specialist II

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!

View solution in original post

8 Replies
jerem1234
Specialist II
Specialist II

It should be like:

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


Hope this helps!

nilesh_gangurde
Partner - Specialist
Partner - Specialist

Try This

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

-Nilesh

Not applicable
Author

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

Not applicable
Author

Maybe try = {"<100"}>}

Anonymous
Not applicable
Author

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.

Not applicable
Author

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

jerem1234
Specialist II
Specialist II

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!

Not applicable
Author

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

Thanks a lot.