Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have been trying to convert Case to IF and i am getting syntax error,
thanks in advance for looking at this.
:
(CASE WHEN `productTypeName`='DisplayBase' THEN (CASE WHEN `Lead Count` IS NOT NULL THEN `Lead Count` ELSE `Lead Volume` END)*`orderCostPerUnit`/1000 ELSE (CASE WHEN `Lead Count` IS NOT NULL THEN `Lead Count` ELSE `Lead Volume` END)*`orderCostPerUnit` END) AS 'Total Order Value'
Hi @Dataguy46
try
Load
.....,
.....,
if(productTypeName='DisplayBase',
(if(not isnull([Lead Count]),[Lead Count],[Lead Volume])*orderCostPerUnit)/1000,
(if(not isnull([Lead Count]),[Lead Count],[Lead Volume])*orderCostPerUnit)
) as [Total Order Value],
....
FROM <where ever>
or, a bit nicer
Load
.....,
.....,
(if(not isnull([Lead Count]),[Lead Count],[Lead Volume])*orderCostPerUnit)/
if(productTypeName='DisplayBase',1000,1) as [Total Order Value],
....
FROM <where ever>
It may have some syntax error because I don't have anywhere to test them right now, but I hope you get the idea.
field name with white spaces or especial characters needs to have [ ] or " "
hope this helps
help users find answers! Don't forget to mark a solution that worked for you & to smash the like button!
Hi @Dataguy46
try
Load
.....,
.....,
if(productTypeName='DisplayBase',
(if(not isnull([Lead Count]),[Lead Count],[Lead Volume])*orderCostPerUnit)/1000,
(if(not isnull([Lead Count]),[Lead Count],[Lead Volume])*orderCostPerUnit)
) as [Total Order Value],
....
FROM <where ever>
or, a bit nicer
Load
.....,
.....,
(if(not isnull([Lead Count]),[Lead Count],[Lead Volume])*orderCostPerUnit)/
if(productTypeName='DisplayBase',1000,1) as [Total Order Value],
....
FROM <where ever>
It may have some syntax error because I don't have anywhere to test them right now, but I hope you get the idea.
field name with white spaces or especial characters needs to have [ ] or " "
hope this helps
help users find answers! Don't forget to mark a solution that worked for you & to smash the like button!