Setting Field Value To Zero

[pnn44794]

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.

[pnn44794]

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))

[lorenzoconforti]

Try wildmatch function

wildmatch ‒ QlikView

Lorenzo

[rwunderlich · Accepted solution]

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

[pnn44794]

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.

[pnn44794]

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?

[rwunderlich]

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

[pnn44794]

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.