topic Expression help please in QlikView

Expression help please

Wed, 25 Nov 2020 16:16:04 GMT

Below expression is not working.What am I doing wrong here ?

if([Lease Type] = 'Owned' and

if ([Lease Type] = 'Our Subtenant' ,

Sum([Rentable Area (sq ft)]*-1)),sum([Rentable Area (sq ft)]))

Thanks much

Re: Expression help please

swuehl — Fri, 06 May 2016 21:57:04 GMT

The syntax is not correct. What do you want to do?

A field value for [Lease Type] can't be 'Owned' AND ''Our Subtenant' at the same time.

Re: Expression help please

Fri, 06 May 2016 22:04:44 GMT

I have data like above.I want to convert the rentable area for 'our Subtenant' to - ve value only if the Building id has lease type owned and Our subtenant else keep it as is.

Re: Expression help please

swuehl — Fri, 06 May 2016 22:12:44 GMT

Maybe like

TMP:

LOAD building, RentableArea, LeaseType

FROM ...;

LEFT JOIN

LOAD building, Max(if( LeaseType= 'Owned',1,0)) AS OwnedFlag

RESIDENT TMP;

LOAD building, RentableArea, LeaseType,

If(OwnedFlag AND LeaseType='Our Subtenant',-1,1)*RentableArea as NewArea

RESIDENT TMP;

DROP TABLE TMP;

Re: Expression help please

MarcoWedel — Fri, 06 May 2016 23:37:47 GMT

please post a sample application

tanks

regards

Marco

Re: Expression help please

ziadm — Sat, 07 May 2016 04:25:25 GMT

Better to use set Analysis in your expression

sum({$<[Lease Type] = {'Owned'},[Lease Type] = {'Our Subtenant'} >}[Rentable Area (sq ft)]*-1)

Re: Expression help please

engishfaque — Sat, 07 May 2016 07:03:06 GMT

Dear Pavana,

Use OR operator instead of AND operator because it is not possible same field held two values at the same time.

Kind regards,

Ishfaque Ahmed

Re: Expression help please

rrsrini2907 — Sat, 07 May 2016 07:15:59 GMT

Hi,

Your condition should be changed like this:

Sum(if([Lease Type] = 'Owned' or [Lease Type] = 'Our Subtenant' ,

([Rentable Area (sq ft)]*-1), ([Rentable Area (sq ft)]))

When you write AND clause on the same field, it will not result true, it will be false.

Try creating this condition at script level without sum function and create it as separate field and use in front end chart.

Regards,

Srini.

Re: Expression help please

swuehl — Sat, 07 May 2016 09:10:24 GMT

The OwnedFlag could probably also created like

LEFT JOIN

LOAD building, 1 AS OwnedFlag

RESIDENT TMP

WHERE LeaseType = 'Owned';

Assuming there is only 1 such Lease Type per building at maximum.

Alternatively, you can use a mapping approach:

MAP:
MAPPING LOAD

building, 1 as OwnedFlag

FROM ....

WHERE LeaseType = 'Owned';

LOAD building, RentableArea, LeaseType,

If(ApplyMap('MAP',building,0) AND LeaseType='Our Subtenant',-1,1)*RentableArea as NewArea

RESIDENT TMP;

DROP TABLE TMP;

In all my suggestions, you may need to adapt the field names to the ones you are using.