If Then...Decode Function

[RptSm]

Does anyone know how to create the if then function in a derived field? I need to create a field that calculates annualized earnings. I keep trying to create the field using the decode function but it isn't working properly.

If PayFreq = B and RateType = S
Then Rate1* 26
Else if
If PayFreq = W and RateType = H
Then (Rate1* 40)*52

thanks

 

[CharlesCook]

This should work



Decode(PayFreq,
'B',Decode(RateType,'S',(Rate1 * 26),0),
'W',Decode(RateType,'H',((Rate1 * 40) * 52),0),
0)

CharlesCook.com
ADP - PeopleSoft - SAP
ReportSmith - Crystal Reports - SQR - Query - Access
Reporting - Interfaces - Data Mining

 

[RptSm]

Thanks but I forgot a part, we have both Salaried employees paid weekly & biweekly plus straight hourly weekly. I need to include all 3 variables. Like the following but it doesn't work. Any suggestions.

Decode("REPORTS"."V_EMPLOYEE"."PAYFRQ",
'W',Decode("REPORTS"."V_EMPLOYEE"."RATETYPE",'S',( "REPORTS"."V_EMPLOYEE"."RATE1AMT" * 52),0),
'B',Decode("REPORTS"."V_EMPLOYEE"."RATETYPE",'S',( "REPORTS"."V_EMPLOYEE"."RATE1AMT"* 26),0),
'W',Decode("REPORTS"."V_EMPLOYEE"."RATETYPE",'H',(( "REPORTS"."V_EMPLOYEE"."RATE1AMT" * 40) * 52),0),
0)

 

[CharlesCook]

The decode would never get to the second "W" code.

This should work



Decode("REPORTS"."V_EMPLOYEE"."PAYFRQ",
'B',Decode("REPORTS"."V_EMPLOYEE"."RATETYPE",'S',("REPORTS"."V_EMPLOYEE"."RATE1AMT" * 26),0),
'W',Decode("REPORTS"."V_EMPLOYEE"."RATETYPE",
'S',("REPORTS"."V_EMPLOYEE"."RATE1AMT" * 52),
'H',(("REPORTS"."V_EMPLOYEE"."RATE1AMT" * 40) * 52),
0),
0)

CharlesCook.com
ADP - PeopleSoft - SAP
ReportSmith - Crystal Reports - SQR - Query - Access
Reporting - Interfaces - Data Mining

 

[RptSm]

Your right. The decode wasn't going to the second W. I will try this thanks and let you know.