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]

HI,

I don't think you cannot avoid IF:

if(len(trim( [Sched Complete Date]))>0, [Sched Complete Date], date(num([Create Date])+45) as [Sched Complete Date]

Good Luck!

[chiru_thota]

[jmonroe918]

Hey Boris:

I'm getting an error (red squiggles) after where the statement is entered. I can't figure out why.

Any idea?

Jeff

LOAD

`Age (Days)`,
`Assigned To`,
`CAPA No`,
Category,
`Close Date`,
`Date Open`,
`Days Open`,
Dept,
`Desc`,
Division,
Origin,

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

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;

[maxgro]

alt(   

     date([Sched Complete Date]),

     date([Create Date]+45)

     ) as [Sched Complete Date]

[chiru_thota]

closing bracket missed.

if(len(trim([Sched Completion Date]))>0,[Sched Completion Date],

date(num([Date Open])+45)) as [Scheduled Completion Date]

[bgerchikov]

Sorry, missed ")"

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

[jmonroe918]

Yeah, I just found it too. However, the data that is replacing the blanks is showing in a five-digit format.

41206, 41264, etc mixed in with the original dates.

How do I fix the format?

Jeff

[bgerchikov]

Just add formatting:

if(len(trim( [SchedCompleteDate]))>0, [SchedCompleteDate], date(num([CreatedDate])+45, 'MM/DD/YYYY')) as [SchedCompleteDate],

[jmonroe918]

I'm still getting the 5-digits.