BeastMode: Field contains lower case

Reply
Highlighted
Black Belt

BeastMode: Field contains lower case

Working on a exception report where item descriptions in our item file contain a lower case character.

 

Logic would work like this:

 

case

when `Description` LIKE '%[lower_case]%' then 'Yes'

else 'No'

end

 

Thanks!


Accepted Solutions
Highlighted
Black Belt

Re: BeastMode: Field contains lower case

@swagner What if you did this?

case
when `Description` = UPPER(`Description`) then 'All upper'
else 'Contains lowercase'
end

If your descriptions come in as all uppercase then it should match on the first when statement. Otherwise it wouldn't match because your description contains a lowercase letter and fall into the else condition.

I haven't tested, but hope it helps.




**Make sure to like any users posts that helped you and accept the ones who solved your issue.**

View solution in original post


All Replies
Highlighted
Green Belt

Re: BeastMode: Field contains lower case

Hi @swagner ,

 

What is the issue here?

 

Thanks,

Neeti

Highlighted
Black Belt

Re: BeastMode: Field contains lower case

@Neeti what are you asking?

Highlighted
Green Belt

Re: BeastMode: Field contains lower case

@swagner , I cant see complete post, was just wondering is the lower case giving you any issue?

 

Regards,

Neeti

Highlighted
Black Belt

Re: BeastMode: Field contains lower case

@Neeti the case statement I provided is simply an example of the logic I am attempting to build with a real beast mode.  This beast mode does not work as is.

Highlighted
Blue Belt

Re: BeastMode: Field contains lower case

@swagner 

I don't think this is doable with a regular expression in a Beast Mode however you can utilize a Magic ETL data flow with a replace text tile and a regex to remove any non-lowercase characters and then check if the value is NULL or not to determine if it doesn't or does have lowercase characters in it.

Screen Shot 2020-03-24 at 9.50.28 AM.png

 

Another option is to utilize a MySQL dataflow instead:

select `holidayDescr`
, case when `holidayDescr` REGEXP '[a-z]' then 'Y' else 'N' end as has_lowercase
from `calendar_dates`

 



**Was this post helpful? Click the heart icon**

**Did this solve your problem? Accept it as a solution!**
Highlighted
Blue Belt

Re: BeastMode: Field contains lower case

@swagner 

 

I suppose technically you could do a really long case statement based on each letter in the alphabet.

 

case when `Description` like '%a%' OR
`Description` like '%b%' OR
`Description` like '%c%' OR
`Description` like '%d%' OR
`Description` like '%e%' OR
`Description` like '%f%' OR
`Description` like '%g%' OR
`Description` like '%h%' OR
`Description` like '%i%' OR
`Description` like '%j%' OR
`Description` like '%k%' OR
`Description` like '%l%' OR
`Description` like '%m%' OR
`Description` like '%n%' OR
`Description` like '%o%' OR
`Description` like '%p%' OR
`Description` like '%q%' OR
`Description` like '%r%' OR
`Description` like '%s%' OR
`Description` like '%t%' OR
`Description` like '%u%' OR
`Description` like '%v%' OR
`Description` like '%w%' OR
`Description` like '%x%' OR
`Description` like '%y%' OR
`Description` like '%z%' THEN 'Y' ELSE 'N' END

 



**Was this post helpful? Click the heart icon**

**Did this solve your problem? Accept it as a solution!**
Highlighted
Black Belt

Re: BeastMode: Field contains lower case

@swagner What if you did this?

case
when `Description` = UPPER(`Description`) then 'All upper'
else 'Contains lowercase'
end

If your descriptions come in as all uppercase then it should match on the first when statement. Otherwise it wouldn't match because your description contains a lowercase letter and fall into the else condition.

I haven't tested, but hope it helps.




**Make sure to like any users posts that helped you and accept the ones who solved your issue.**

View solution in original post

Highlighted
Blue Belt

Re: BeastMode: Field contains lower case

Of course I overthink the whole thing. @MarkSnodgrass has a much cleaner solution.



**Was this post helpful? Click the heart icon**

**Did this solve your problem? Accept it as a solution!**
Highlighted
Black Belt

Re: BeastMode: Field contains lower case

@MarkSnodgrass THANKS!

Announcements
Want to join our weekly online meet up? Please visit this board here and let me know. Click here for more details!