topic Re: Adding Days to Date Field During Load in QlikView

Adding Days to Date Field During Load

jmonroe918 — Thu, 13 Mar 2014 22:16:49 GMT

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

Re: Adding Days to Date Field During Load

bgerchikov — Thu, 13 Mar 2014 22:23:33 GMT

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!

Re: Adding Days to Date Field During Load

chiru_thota — Thu, 13 Mar 2014 22:36:59 GMT

Re: Adding Days to Date Field During Load

jmonroe918 — Thu, 13 Mar 2014 22:49:15 GMT

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;

Re: Adding Days to Date Field During Load

maxgro — Thu, 13 Mar 2014 22:50:57 GMT

alt(

date([Sched Complete Date]),

date([Create Date]+45)

) as [Sched Complete Date]

Re: Adding Days to Date Field During Load

chiru_thota — Thu, 13 Mar 2014 22:51:37 GMT

closing bracket missed.

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

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

Re: Adding Days to Date Field During Load

bgerchikov — Thu, 13 Mar 2014 23:02:58 GMT

Sorry, missed ")"

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

Re: Adding Days to Date Field During Load

jmonroe918 — Thu, 13 Mar 2014 23:06:46 GMT

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

Re: Adding Days to Date Field During Load

bgerchikov — Thu, 13 Mar 2014 23:19:40 GMT

Just add formatting:

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

Re: Adding Days to Date Field During Load

jmonroe918 — Thu, 13 Mar 2014 23:25:38 GMT

I'm still getting the 5-digits.

Re: Adding Days to Date Field During Load

bgerchikov — Thu, 13 Mar 2014 23:43:14 GMT

Please check date format settings. it should be

SET DateFormat='M/D/YYYY';

Please review attached

Re: Adding Days to Date Field During Load

bgerchikov — Thu, 13 Mar 2014 23:55:58 GMT

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];