Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi there,
During the load process I'm assessing if a change in record occurs at the end of the month and if it does not force it to be the end of the previous month in a new column End Date Alt.
I've attempted to do this using an if statement. I've added an extra column "Check" to assess why I'm having a problem.
See below:
[Data]:
Load *
, if(
date([End Date],'DD/MM/YYYY') = MonthEnd(date([End Date],'DD/MM/YYYY'))
, date([End Date],'DD/MM/YYYY')
, MonthEnd(addmonths([End Date],-1))
) as [End Date Alt]
, MonthEnd([End Date]) as Check;
Load * Inline [
'End Date'
31/10/2016,
04/10/2016,
29/02/2016
];
The if statement is not assessing the dates as I would have expected and I'm stumped.
The End Date matches the Check column for both the 29th and the 31st and yet the If statement does not resolve that way. Clearly I'm doing something wrong.
End Date | End Date Alt | Check |
---|---|---|
29/02/2016 | 31/01/2016 | 29/02/2016 |
04/10/2016 | 30/09/2016 | 31/10/2016 |
31/10/2016 | 30/09/2016 | 31/10/2016 |
Help!
Returns a value corresponding to a timestamp with the last millisecond of the last date of the month containing date. The default output format will be the DateFormat set in the script. Shift is an integer, where the value 0 indicates the month which contains date. Negative values in shift indicate preceding months and positive values indicate succeeding months.
Examples:
monthend ( '2001-02-19' ) returns '2001-02-28' with an underlying numeric value corresponding to '2001-02-28 23:59:59.999'
monthend ( '2001-02-19', -1 ) returns '2001-01-31' with an underlying numeric value corresponding to '2001-01-31 23:59:59.999'
So you need to compare to
if(
Floor([End Date]) = Floor(MonthEnd( [End Date])),
....
[Date() formatting is not needed in numerical comparisons]
MonthEnd() function outputs a TimeStamp, I think you will need to use Floor() with it to give a desired output here:
[Data]:
Load *
, if(
date([End Date],'DD/MM/YYYY') = Floor(MonthEnd(date([End Date],'DD/MM/YYYY')))
, date([End Date],'DD/MM/YYYY')
, MonthEnd(addmonths([End Date],-1))
) as [End Date Alt]
, MonthEnd([End Date]) as Check;
Load * Inline [
'End Date'
31/10/2016,
04/10/2016,
29/02/2016
];
Returns a value corresponding to a timestamp with the last millisecond of the last date of the month containing date. The default output format will be the DateFormat set in the script. Shift is an integer, where the value 0 indicates the month which contains date. Negative values in shift indicate preceding months and positive values indicate succeeding months.
Examples:
monthend ( '2001-02-19' ) returns '2001-02-28' with an underlying numeric value corresponding to '2001-02-28 23:59:59.999'
monthend ( '2001-02-19', -1 ) returns '2001-01-31' with an underlying numeric value corresponding to '2001-01-31 23:59:59.999'
So you need to compare to
if(
Floor([End Date]) = Floor(MonthEnd( [End Date])),
....
[Date() formatting is not needed in numerical comparisons]
Thanks Both Floor did the trick. I will have to read up on that a bit more