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!
@swagner What if you did this?
casewhen `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.
Hi @swagner ,
What is the issue here?
Thanks,
Neeti
@Neeti what are you asking?
@swagner , I cant see complete post, was just wondering is the lower case giving you any issue?
Regards,
@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.
@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.
Another option is to utilize a MySQL dataflow instead:
select `holidayDescr`, case when `holidayDescr` REGEXP '[a-z]' then 'Y' else 'N' end as has_lowercasefrom `calendar_dates`
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
Of course I overthink the whole thing. @MarkSnodgrass has a much cleaner solution.
@MarkSnodgrass THANKS!
