topic Re: Setting Field Value To Zero in QlikView

Setting Field Value To Zero

pnn44794 — Wed, 25 Nov 2020 16:16:04 GMT

Hello:

I have a straight table with multiple columns, but for this question, there are columns in play and they are COMP (describes a storage tier), ASSET_COUNT (number of assets for the row) and NET_ASSETS (could be a negative number (for removes), a positive number or zero).

For rows that have a COMP value starting with SAN* or NAS*, I want to set ASSET_COUNT and NET_ASSETS to zero as I do not want to count assets for the storage rows whether it be a remove or add.

I've tried If(COMP = 'SAN*' or COMP = 'NAS*', NET_ASSETS = 0, ASSET_COUNT = 0) , which does not appear to do anything. Does anyone have any suggestions? Thanks in advance.

Re: Setting Field Value To Zero

pnn44794 — Mon, 02 Oct 2017 19:00:43 GMT

I should add that I have a second expression which does sum the rows I want to sum for an asset count and that expression is:

=if(NET_ASSETS > 0 or NET_ASSETS < 0, Sum(NET_ASSETS), Sum(ASSET_COUNT))

Re: Setting Field Value To Zero

lorenzoconforti — Mon, 02 Oct 2017 22:01:10 GMT

Try wildmatch function

wildmatch ‒ QlikView

Lorenzo

Re: Setting Field Value To Zero

rwunderlich — Mon, 02 Oct 2017 22:02:05 GMT

1. You should create separate expressions (columns) for NET_ASSETS and ASSET_COUNT.

2. When using wildcard in a string test, the operator is LIKE, not "=". You can also use the WildMatch() function which is a bit easier when looking for multiple values.

So your NET_ASSETS expression will look like this:

sum(if(WildMatch(COMP, 'SAN*', 'NAS*'), 0, NET_ASSETS)

or the same thing in Set Analysis syntax (which should be more efficient):

sum({<COMP-={"SAN*", "NAS*"}>} NET_ASSETS)

Your ASSET_COUNT expression is the same, substituting ASSET_COUNT for the field name.

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Re: Setting Field Value To Zero

pnn44794 — Mon, 02 Oct 2017 23:26:24 GMT

Hello Lorenzo:

Thank you very much for the response. I appreciate it. I've bookmarked the link which I'm sure I'll reference in the future. Again, I appreciate you taking the time to respond.

Re: Setting Field Value To Zero

pnn44794 — Mon, 02 Oct 2017 23:37:24 GMT

Hello Rob:

Thank you for taking the time to respond. I appreciate it.

I do have two distinct columns, one for NET_ASSETS and one for ASSET_COUNT. Sorry I wasn't clear on that.

Both of your solutions work for me. Thank you. To make sure I understand (and hopefully learn something), when you say LIKE (I'm used to that type of syntax in SQL), (WILDMATCH(COMP, 'SAN*', 'NAS*') says when there's a LIKE match of either SAN* or NAS* for COMP, then set the value of NET_ASSETS to 0. Is that right?

And for set analysis, it essentially says the same thing except only sum NET_ASSETS if COMP is LIKE SAN* or NAS*. Correct?

Lastly, I shouldn't try to combine NET_ASSETS and ASSET_COUNT in the same expression, even though they are separate columns?

Re: Setting Field Value To Zero

rwunderlich — Tue, 03 Oct 2017 13:30:58 GMT

Hi Perry,

My reference to LIKE was as an operator in the first statement you gave.

if(COMP LIKE '*NAS' OR COMP LIKE '*SAN', ...

LIKE (as apposed to =), as in SQL, indicates * and ? in the argument that follow should be considered wildcards.

COMP = '*'NAS' would be looking for a literal asterisk, not a wildcard.

The WildMatch function is just a more convenient form of testing multiple values instead of having to write OR. Yes, your understanding is correct, although I might phrase it differently than "set the value".

"If there is a match, add 0 to the sum. Otherwise add the value of NET_ASSETS to the sum.".

It may also be written with a NOT operator like this:

sum(if(NOT WildMatch(COMP, 'SAN*', 'NAS*'),NET_ASSETS)

meaning "If there is no match, add the value of NET_ASSETS to the sum. Otherwise add null (effectively zero) to the sum."

Set Analysis syntax is like making or modifying selections before doing the aggregation.

sum({<COMP-={"SAN*", "NAS*"}>} NET_ASSETS)

The -= operator means "remove (exclude) these values from the current selections". The double quotes " indicate the value list contains wildcards. So rows matching those COMP values are first removed, then NET_ASSETS values are summed for the remaining rows.

Yes, you should sum NET_ASSETS and ASSET_COUNT in separate expressions.

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Re: Setting Field Value To Zero

pnn44794 — Thu, 05 Oct 2017 19:27:00 GMT

Hello Rob:

My apologies for the delay in replying. It's been a crazy week.

Thank you for the explanation. I appreciate it. I've marked your first response as the correct answer. Thanks again.