Missing Date Vaules

[ciaran_mcgowan]

Hi all,

I have created a master calendar but there are some dates that are not loading into it. After investigating, I found the reason is because some of the dates are not complete and there is no way to change this from the data source.

Some dates are missing days and other are also missing months (below are the 3 types of date values)

03-JAN-2010

xx-NOV-2012

xx-xxx-2013

The problem is that I converted these text fields to date values before creating the master (below)

Date(Date#(Date,'DD-MMM-YYYY'),'DD/MM/YYYY')

but the master calendar just ignores them. That means that the values that are only missing days are not appearing in their corresponding Month when looking at an overview of the Year, they are displayed as Null dates.

[sunny_talwar]

What exactly does your missing day and Day Month Field look like??

NOV-2012

2013

or

xx-NOV-2012

xx-xxx-2013

or

Something else???

[ciaran_mcgowan]

xx-NOV-2012

xx-xxx-2013

The text & numbers are replaced with x

[sunny_talwar]

May be this:

Date(Alt(Date#(Date,'DD-MMM-YYYY'),'DD/MM/YYYY'), MakeDate(Right(Date, 4), If(Mid(Date, 3, 3) = 'xxx', 1, Mid(Date, 3, 3)), If(Left(Date, 2) = 'xx', 1, Left(Date, 2)))) as Date

You might need to check for the parenthesis.

HTH

Best,

Sunny

[jagan]

Hi,

Try like this

TableName:

LOAD

*,

MakeDate(SubField(NewDate, '-', 3),

Match(If(Len(Trim(SubField(NewDate, '-', 2))) = 0, 'JAN', SubField(NewDate, '-', 2)), 'JAN', 'FEB', 'MAR',  'APR',  'MAY',  'JUN',  'JUL',  'AUG', 'SEP',  'OCT',  'NOV',  'DEC'),

Alt(Num(SubField(NewDate, '-', 1)), 1)) AS NewFormattedDate;

LOAD

*,

Replace(DateFieldName, 'x', '') AS NewDate

FROM DataSource;

Hope this helps you.

Regards,

Jagan.

[ciaran_mcgowan · Accepted solution]

Thanks for the help.

I was hoping to avoid setting unknown months and days to the beginning of the Month/Year but it looks like it can't be avoided. I already had a check to replace one incorrect date earlier in the script (below)

Replace(Date,'91','xx') AS Date 

So I just adapted that:

Replace(Replace(Replace(Date,'91',1),'xxx','JAN'),'xx',1) AS Date

That's done the trick.