topic Re: If then statement, BUT result could sit in both camps problem in QlikView

If then statement, BUT result could sit in both camps problem

dinicholls — Thu, 20 Nov 2014 16:27:05 GMT

Hi All,

I have various excel formula / reports / horror stories that I'm trying to convert into QlikView reports.

I've come across a bit of a problem.

These are my parameters that define wether a record is Revenue, SSC Cash, Non SSC Cash. I need to group on the results of these records.

The problem being that depending on on the record, it could actually sit in two camps, SSC Cash AND Non SSC Cash. I'm using and 'If' statement at the moment, but just realised that it doesn't like it! Here's my criteria:

Revenue: DOC TYPE is one of SLINV or SLCRD

SSC Cash: DOC TYPE is one of SLCSH or SLJNL

AND

DOC ID is not one of ARCW01, ARCWO1, ARCWOF, ARDEBT, ARRFND, ASTO or is not the 1st letter U,V,W,X,Y,Z

Non SSC Cash: DOC TYPE = SLCSH OR

DOC TYPE = SLJNL AND DOC ID is one of ARMJNL, ARICJL, SJTR

Does that make sense?!

Any ideas how to overcome this?

Thanks

Re: If then statement, BUT result could sit in both camps problem

anbu1984 — Thu, 20 Nov 2014 17:07:56 GMT

Load *,(If([DOC TYPE]='SLINV' Or [DOC TYPE]='SLCRD','Revenue')) & (If([DOC TYPE]='SLCSH' Or [DOC TYPE]='SLJNL','SSC Cash;')) &

(If([DOC TYPE]='SLCSH' Or ([DOC TYPE]='SLJNL' And [DOC ID]='ARICJL'),'Non SSC Cash')) As Type Inline [

DOC TYPE,DOC ID

SLINV,A

SLCRD,B

SLCSH,ARMJNL

SLJNL,ARICJL ];

Re: If then statement, BUT result could sit in both camps problem

Thu, 20 Nov 2014 19:11:32 GMT

Hi Diane,

See attached application.

and in your conditions, Non SSC Cash is contradicting with the IF condition of SSC cash.

SSC Cash should be like this:

SSC Cash: DOC TYPE is one of SLCSH or SLJNL

AND

DOC ID is not one of ARCW01, ARCWO1, ARCWOF, ARDEBT, ARRFND, ASTO, ARMJNL, ARICJL, SJTR or is not the 1st letter U,V,W,X,Y,Z.

Hope this helps.