Nested If Statement -MALALIGNED

[aesham]

Hello All -- 

I would appreciate some help.  I am trying to create a somewhat complicated Nested-If Statement.

Here's is a sample of what the data line looks like

The LOW_ and HIGH_ price range are for the size specified in SIZE.  So, on line 1 we see the size/ranges are for 375ml and we see there is a price in the 375$ column, so we test if the 375$ price of $4.99 lies within the range, if it does we accept the $4.99, however if it is outside the range we replace the 375$ with 0.

Then we look at the next record, again, the SIZE is also for a 375ml so we repeat the process above.

In the third record, the SIZE is 750ml and we check the 750$ to see if there is a price.  There is a price so we test to see if that price falls within the range.  If it does we keep the 750$ price; if it does not we zero it out.

I am able to do this as multiple separate calculations but think this could be done efficiently as a Nested-IF.  Unfortunately I keep getting syntax errors ("Malaligned IF Statement" on the first ELSEIF) and am unable to find a good reference for how to build this.  Here's what I have:

IF [SIZE] = '375ml' AND [375$] > 0
THEN IF [375$] >= [LOW_PRICE] AND [375$]<= [HIGH_PRICE]
THEN [375$] ELSE 0
ELSEIF [SIZE] = '750ml' AND [750$] > 0
THEN IF [750$] >= [LOW_PRICE] AND [750$] <=[HIGH_PRICE]
THEN [750$] ELSE 0
ELSEIF [SIZE] = '1.0l' AND [1.0l] > 0
THEN IF [1.0L] >= [LOW_PRICE] AND [1.0l] <= [HIGH_PRICE]
THEN [1.0l] ELSE 0
ENDIF
END

Any help would be greatly appreciated.  Thanks!

Andrew

[RodL · Accepted solution]

Would this work?

IF [SIZE] = '375ml' AND [375$] > 0 AND [375$] >= [LOW_PRICE] AND [375$]<= [HIGH_PRICE] THEN [375$]
ELSEIF [SIZE] = '750ml' AND [750$] > 0 AND [750$] >= [LOW_PRICE] AND [750$] <=[HIGH_PRICE] THEN [750$]
ELSEIF [SIZE] = '1.0l' AND [1.0l] > 0 AND [1.0L] >= [LOW_PRICE] AND [1.0l] <= [HIGH_PRICE] THEN [1.0l]

ELSE 0
ENDIF

[MarqueeCrew]

I was kind of thinking that you might transpose the first four columns and use brand for the key. You would trim off the $ and get:

Brand, name, value
A, 375, 4.99
A, 750, 8.99
A, 1, 0
...
Join that back on brand, name to the data AFTER you trim ML from Size to brand, size

You can then check value GE low price && value LE high price.

Just a thought.

From my iPhone.

[MarqueeCrew]

I made this a bit more dynamic.  I've got a module for you to look at.  @RodL, what are your thoughts?

[jdunkerley79 · Accepted solution]

I like this approach.

I would also suggest simple formula based approach, by splitting into two steps. Create a double field called PriceUsed

SWITCH([SIZE], 0, '375ml', [375$], '750ml', [750$], '1.0l', [1.0l]) 

Then Second step change PriceUsed to be

IF [PriceUsed] > 0 AND [PriceUsed] >= [LOW_PRICE] AND [PriceUsed] <= [HIGH_PRICE] THEN
    [PriceUsed]
ELSE
   0
ENDIF

[aesham]

Thank you for this solution.  I think this will work!  Good to know I was in the ballpark but just needed to reorder the logic.  Much appreciated.

[aesham]

Thank you for your timely response. I was completely unaware of the Switch function and this is truly a unique solution.  I am going to give it a try along with the first solution offered - to test them both out.  Much appreciated.