Custom Column with 'M' ('and' with double 'or' condition) Help Needed

[Anonymous]

Hi All!

3 month lurker, first time poster.  Needing a little help with a buildout of a custom column used as a market identifier to separate work streams. 

I feel like I'm super close, but I'm going round and round trying to get a proper result and can't seem to lock it in.

The solution is this:

Mark '1' for any county in the Charlotte, Philly, or VB metros.  Also mark '1' for any county in the Atlanta metro, with the exception of dekalb and fulton.

The M I've got looks like this:

...but I'm not getting the expected result. It seems to be ignoring the county identifier altogether and defaulting ALL Atlanta counties to '1':

If I yank either side of the 'or' however... (in this case "fulton")

...it does perform as expected and removes the dekalb entries.

Can anyone identify what I'm overlooking in the M syntax to put this to rest?  Am I backwards somehow on the order of operations?

Appreciate the insight!

[Nathaniel_C]

@Anonymous ,

Nicely laid out issue! Not sure of the answer, but from a pure testing point of view, maybe try if Atlanta and not dekalb, or if atlanta and not fulton. Change the order of the test. Or if you have time, give the results different numbers for testing.

If this solves it great, if not, maybe reach out to imkeF.  She is a magician with m.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

[Anonymous]

@Nathaniel_C 

Thanks for the follow-up! 

Sadly, yes, that combination has been tried along with any other order I could think of that made sense programmatically.

Appreciate the referral, I'll definitely reach out while I keep digging.

[jdbuchanan71 · Accepted solution]

@Anonymous 

What if you split the first test into two and reverse them like so:

if 
	[Metro] = "Atlanta" and [county_name] = "dekalb" then 0
else if 
	[Metro] = "Atlanta" and [county_name] = "fulton" then 0
else if
	[Metro] = "Atlanta" or
	[Metro]	= "Charlotte" or
	[Metro]	= "Philadelphia" or
	[Metro]	= "Virginia Beach"
then
	1
else
	0

Does that work?

[Anonymous]

Hey @jdbuchanan71 !

I believe that's one of the combos I've tried...same result:

It's becoming comical at this point 🙂

[jdbuchanan71]

@Anonymous 

Reverse the test to [county_name] = "dekalb" then 0.

In your example, every county not "dekalb" (including "fulton") would get a 1.

Could this also be a problem with the case?  Would you be better off using [county upper]?

[Anonymous]

@jdbuchanan71   yep, just saw your updated test:

Here's the result:

...so, consistency in the results returned...just not the correct ones.... ha!

I haven't run the logic against the UPPER modified column, I'll give that a shot too

[jdbuchanan71]

@Anonymous 

You need to change <> to = then return 0

[Anonymous]

@jdbuchanan71   @Nathaniel_C 

Duuuuuuuuuuuuuuuuude.

We got there!  Appreciate the assistance guys.

[Nathaniel_C]

I am always trying to solve, if for some reason the m language has a glitch, then how about intermediate columns for part of the logic, and then gather up the results in this column?

Nathaniel