Adding Days to Date Field During Load

[jmonroe918]

I have a date field that is loaded called [Sched Complete Date]. Sometimes the field is blank (and it messes up some of my formulas). What I want to do is, when the Sched Complete Date is empty, add 45 days from another field in data; Create Date. Example:

Create Date = 1/1/2014

Sched Complete Date is updated to 2/14/2014

I can't figure out the structure of the "IF/Then" statement.

Thanks for any help.

Jeff

[bgerchikov]

Please check date format settings. it should be

SET DateFormat='M/D/YYYY';

Please review attached

[bgerchikov]

It might happened because you perform it in SQL.

Try the following:

1. Create Temp table out of SQL Load

2. Create Fact table Residence Temp with IF

TEMP:

LOAD

`Age (Days)`,
`Assigned To`,
`CAPA No`,
Category,
`Close Date`,
`Date Open`,
`Days Open`,
Dept,
`Desc`,
Division,
Origin,
'Sched Completion Date' as [Temp Sched Completion Date],

'Date Open' as [Date Open],
Source,
Status,
Type,
Date(yearstart([Date Open]), 'YYYY') as YearOpen,
Date(yearstart([Close Date]), 'YYYY/MM') as YearClose,
Date(monthstart([Date Open]), 'YYYY/MM') as YrMon_Open,
Date(monthstart([Close Date]), 'YYYY/MM') as YrMon_Close,;

SQL SELECT *
FROM qryCAPA;

FACT:

LOAD *,

if(len(trim([Temp Sched Completion Date]))>0,[Temp Sched Completion Date], date(num([Date Open])+45)) as [Scheduled Completion Date]

Resident TEMP

;

drop table TEMP;

drop field [Temp Sched Completion Date];