Thursday 5 July 2012

IsNumeric or NOT

I needed to show the column values containing numbers as 'Numeric' and the values without the numbers as 'Alphabets'. I would first validate the column values with the case statement, if it is containing any numeric value. If it doesnt contain then it is named as 'Alphabets'
And on top of it I need to show the custom messages back ground with different colors.
I have tried the below method with HTML and with conditional formatting.

First method approach is with HTML and case statement.

case when ("Products"."Product" like '%1%')
OR ("Products"."Product" like '%2%')
OR ("Products"."Product" like '%3%')
OR ("Products"."Product" like '%4%')
OR ("Products"."Product" like '%5%')
OR ("Products"."Product" like '%6%')
OR ("Products"."Product" like '%7%')
OR ("Products"."Product" like '%8%')
OR ("Products"."Product" like '%9%')
OR ("Products"."Product" like '%0%')
then '<span style="background-color:Red">Numeric</span>'
else '<span style="background-color:Green">Alphabets</span>'
end



Second method with conditional formatting and case statement.

Here you need to add the below code in the column formula

case when ("Products"."Product" like '%1%')
OR ("Products"."Product" like '%2%')
OR ("Products"."Product" like '%3%')
OR ("Products"."Product" like '%4%')
OR ("Products"."Product" like '%5%')
OR ("Products"."Product" like '%6%')
OR ("Products"."Product" like '%7%')
OR ("Products"."Product" like '%8%')
OR ("Products"."Product" like '%9%')
OR ("Products"."Product" like '%0%')
then 'Numeric'
else 'Alphabets'
end

and in the column properties-->conditional formatting-->add condition




Save and check the results...

Comments are always welcome...


No comments:

Post a Comment

Thanks to Comment
--Add--