Initialize Numeric fields

[tmumaw]

Morning everyone,

I am trying to initialize my dollar values with zero so I can sum my dollar values and for some reason it still thinks it's has null values.  Here is my table and how I'm trying to set values to zeros.  Am I missing something?

Opportunity:
LOAD
Oppt.AccountId as Acct.AccountId,
Oppt.OpportunityId,
Oppt.StateCode,
Oppt.StatusCode, 
Oppt.new_Salesperson,
Oppt.ActualCloseDate as [Act_Date],
Oppt.EstimatedCloseDate as [Est_Date], 
Oppt.new_CostCenter,
if(Oppt.new_SalesType = '70330350-FFB1-E411-80D6-005056801CBD','RWLS',
if(Oppt.new_SalesType = '72330350-FFB1-E411-80D6-005056801CBD','SNOW',
if(Oppt.new_SalesType = '14CF7AFA-30E0-E711-8102-005056801CBD','SNOW', 
if(Oppt.new_SalesType = 'D4CB552F-B0F4-E711-8104-005056801CBD','SNOW', 
if(Oppt.new_SalesType = '6A330350-FFB1-E411-80D6-005056801CBD','ENMT',
if(Oppt.new_SalesType = 'AEC295B7-7EDE-E611-80F4-005056801CBD','NEW',
if(Oppt.new_SalesType = 'AA642C47-1920-E511-80DB-005056801CBD','OTH',
if(Oppt.new_SalesType = '5B3F1037-180E-E511-80DA-005056801CBD','OTH',
if(Oppt.new_SalesType = '6C330350-FFB1-E411-80D6-005056801CBD','OTH',
if(Oppt.new_SalesType = '6E330350-FFB1-E411-80D6-005056801CBD','OTH',
if(Oppt.new_SalesType = '238BF323-180E-E511-80DA-005056801CBD','OTH',
if(Oppt.new_SalesType = '6360121C-180E-E511-80DA-005056801CBD','OTH',
if(Oppt.new_SalesType = 'AA642C47-1920-E511-80DB-005056801CBD','OTH',
if(Oppt.new_SalesType = 'B1F4532B-180E-E511-80DA-005056801CBD','OTH','')))))))))))))) as SType,
if(Oppt.new_SalesType = '70330350-FFB1-E411-80D6-005056801CBD' or
Oppt.new_SalesType = 'AEC295B7-7EDE-E611-80F4-005056801CBD',1,0) as NewSales,
if(Oppt.new_SalesType = '6A330350-FFB1-E411-80D6-005056801CBD',1,0) as EnmtSales,
If(Oppt.EstimatedValue > 0,Oppt.EstimatedValue,0) as EstAmt,
If(Oppt.ActualValue > 0,Oppt.ActualValue,0) as ActAmt,
If(Oppt.new_PendingAmount > 0,Oppt.new_PendingAmount,0) as PendingAmt,
If(Oppt.new_LostAmount > 0,Oppt.new_LostAmount,0) as LostAmt  
FROM

(qvd);

[CarlosAMonroy]

Well I guess that's because if it's null the condition is not met. You're saying put 0 if values are less than 0 not if values are nulls, right? I'm not sure if I got your question.

But you can do this:

if(len(Oppt.ActualValue) = 0 or isnull(Oppt.ActualValue) or Oppt.ActualValue='', 0, Oppt.ActualValue) as ActAmt

Hope that Helps,

Carlos M

[marcus_sommer]

In regard of summing a field is it not mandatory necessary to convert any NULL values to 0. Of course there are scenarios in which it may useful but there might be drawbacks if you want to count or average over these fields. Nevertheless if you want to do it you could use something like this:

alt(Oppt.EstimatedValue,0) as EstAmt

Beside this I suggest to use a mapping instead of a nested if-loop to categorise your sales type.

- Marcus