Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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???
xx-NOV-2012
xx-xxx-2013
The text & numbers are replaced with x
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
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.
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.