topic Re: Field value less or equal a hard coded value in QlikView

Field value less or equal a hard coded value

Tue, 25 Mar 2014 13:43:37 GMT

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?

Re: Field value less or equal a hard coded value

jerem1234 — Tue, 25 Mar 2014 13:45:36 GMT

It should be like:

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

Hope this helps!

Re: Field value less or equal a hard coded value

nilesh_gangurde — Tue, 25 Mar 2014 13:46:43 GMT

Try This

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

-Nilesh

Re: Field value less or equal a hard coded value

Tue, 25 Mar 2014 13:49:11 GMT

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

Re: Field value less or equal a hard coded value

Tue, 25 Mar 2014 13:54:07 GMT

Maybe try = {"<100"}>}

Re: Field value less or equal a hard coded value

Anonymous — Tue, 25 Mar 2014 13:55:21 GMT

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.

Re: Field value less or equal a hard coded value

Tue, 25 Mar 2014 13:56:04 GMT

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

Re: Field value less or equal a hard coded value

jerem1234 — Tue, 25 Mar 2014 13:57:49 GMT

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!

Re: Field value less or equal a hard coded value

Tue, 25 Mar 2014 14:03:04 GMT

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

Thanks a lot.