Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

Expression help please

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

1 Solution

Accepted Solutions
rrsrini2907
Contributor

Re: Expression help please

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.

8 Replies
MVP
MVP

Re: Expression help please

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.

Not applicable

Re: Expression help please

Capture.PNG

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.

MVP
MVP

Re: Expression help please

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

please post a sample application

tanks

regards

Marco

ziadm
Valued Contributor

Re: Expression help please

Better to use set Analysis in your expression

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

engishfaque
Valued Contributor III

Re: Expression help please

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

rrsrini2907
Contributor

Re: Expression help please

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.

Highlighted
MVP
MVP

Re: Expression help please

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.