Expression help please

[#1]

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

[swuehl]

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.

[#3]

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.

[swuehl]

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;

[MarcoWedel]

please post a sample application

tanks

regards

Marco

[ziadm]

Better to use set Analysis in your expression

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

[engishfaque]

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 · Accepted solution]

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.

[swuehl]

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.