Table with Set Analysis expression: Identify specific characters and remove from Total ($)

[Brett_WP]

Hi all, after a little help as can't seem to find what I'm looking for.

I have a list of Project Codes with 'WIP' amounts attached. All codes begin with 2 x letters, followed by a series of numbers i.e.

AB12345       $1000

BC54321       $5000

ZA55567       $3200 etc...

There are some Project Codes however, that only begin with 1 x letter i.e. N02345, B098765, N755547 etc.

I currently use SET ANALYSIS to identify WIP:     sum({$<WORD_STATUS = {"WIP"}, COST_MIX = {*} >} TOT_AMT)

I'm not sure if I can just add to this expression or need to go another way?

I want to do the following: ... When a Project Code begins with only 1 x letter, then exclude those Project Codes from TOT_AMT. This will leave me with only the WIP Amounts for Project Codes with 2 x letters followed by a number series.

The letter could be any letter from the Alphabet so needs to include some kind of Wild Card.

Thanks in advance.

[Anonymous]

you may either define a flag in your script and use this flag in set analysis like FlagPC1Char=IsNum(ProjectCode,2,1)
and add sum({$<WORD_STATUS = {"WIP"}, COST_MIX = {*} ,FlagPC1Char={0}>} TOT_AMT)
Check if second character only contains numbers which you want to exclude

or you use an if statement
if (IsNum(ProjectCode,2,1)=0, sum({$<WORD_STATUS = {"WIP"}, COST_MIX = {*} >} TOT_AMT))

[Brett_WP · Accepted solution]

I took your advice and defined a flag in my script - thanks for pointing me in the right direction, I'm still new at this.

However I used:

LOAD

ProjectCode,

case
when regexp_like(ProjectCode,'^[A-Z]{2}') then 'Y'
else 'N'
end as Flag,

Then as you suggested excluded this using set analysis: sum({$<WORD_STATUS = {"WIP"},FLAG -= {"Y"}>}TOT_AMT)

This solved my issue.

I am curious though, I wasn't able to get the IsNum function to work. Both Qlik and SQL would not accept multiple parameters in the isnum function as you listed in your reply. Am I missing something that would cause this not to work?

Thanks again for your help.

[Anonymous]

isnum doesnot have multiple Parameters
with the mid function I extract the second character (and only the second character) and test for number
Can you post your Code wich doesnot work?
Testcode: =isnum(mid('A21234',2,1))  or =isnum(mid(ProjectCode,2,1)) 

[Brett_WP]

Great, that makes sense. In the original reply the mid function wasn't in the formula so that explains my issue with isnum.

I've tried the correction, however I don't get the correct number. I get the whole "WIP" value as I would with just the Set Analysis.

i.e. sum({$<WORD_STATUS = {"WIP"}, COST_MIX = {*}>} TOT_AMT))

Returns $3.5M

if(isnum(mid(ProjectCode,2,1))=0,sum({$<WORD_STATUS = {"WIP"}, COST_MIX = {*}>} TOT_AMT))

Returns $3.5M

This is still including all ProjectCodes... if working correctly should pull out ~$1M from the TOT_AMT.

Thanks.

[Anonymous]

The isnum doesnot recognize a number?
can you use the Code isnum(mid(ProjectCode,2,1)) while selecting
different ProjectCodes (one with a character in second place and one with a number
in second place)
try with TextBox or with a Formula in listbox (or post your qvw)

[Brett_WP]

Seems to be the set analysis portion of the formula that's causing the issue.

Using the following, returns the correct value: sum(if(isnum(mid(PRNT_PROJ_NO,2,1))=0 AND WORD_STATUS = 'WIP',TOT_AMT))

This shows me the value of all ProjectCodes that begin with 2 x letters and removes all those that don't meet that criteria.

The IsNum function works correctly:

isnum(mid('N0437433',2,1)) returns 1 

isnum(mid('MA013736',2,1)) returns 0

Either way, problem solved and I really appreciate the help.

I'll have to look more into why the Set Analysis is causing problems. A little bit of trial and error to get to the issue.

Thanks again.