Something that came up on OTN:
Cast a metric (number) as char but still have the thousand separator. E.g.: if "Sales" > 1000 then "Nothing to report" else 1'000?
Here's a formula you can use:
case when (LENGTH(cast(Fact.Sales as char)) > 12) then
(SUBSTRING(cast(Fact.Sales as char) FROM LENGTH(cast(Fact.Sales as char)) - 15 FOR 3) || '''')
else '' END ||
case when (LENGTH(cast(Fact.Sales as char)) > 9) then
SUBSTRING(cast(Fact.Sales as char) FROM LENGTH(cast(Fact.Sales as char)) - 12 FOR 3) || ''''
else '' END ||
case when (LENGTH(cast(Fact.Sales as char)) > 6) then
SUBSTRING(cast(Fact.Sales as char) FROM LENGTH(cast(Fact.Sales as char)) - 9 FOR 3) || ''''
else '' END ||
case when (LENGTH(cast(Fact.Sales as char)) > 0) then
SUBSTRING(cast(Fact.Sales as char) FROM LENGTH(cast(Fact.Sales as char)) - 6 FOR 7)
else 'Nothing to report' END
End of line.
Cast a metric (number) as char but still have the thousand separator. E.g.: if "Sales" > 1000 then "Nothing to report" else 1'000?
Here's a formula you can use:
case when (LENGTH(cast(Fact.Sales as char)) > 12) then
(SUBSTRING(cast(Fact.Sales as char) FROM LENGTH(cast(Fact.Sales as char)) - 15 FOR 3) || '''')
else '' END ||
case when (LENGTH(cast(Fact.Sales as char)) > 9) then
SUBSTRING(cast(Fact.Sales as char) FROM LENGTH(cast(Fact.Sales as char)) - 12 FOR 3) || ''''
else '' END ||
case when (LENGTH(cast(Fact.Sales as char)) > 6) then
SUBSTRING(cast(Fact.Sales as char) FROM LENGTH(cast(Fact.Sales as char)) - 9 FOR 3) || ''''
else '' END ||
case when (LENGTH(cast(Fact.Sales as char)) > 0) then
SUBSTRING(cast(Fact.Sales as char) FROM LENGTH(cast(Fact.Sales as char)) - 6 FOR 7)
else 'Nothing to report' END
End of line.
Posts
