topic Re: Script issue with an If statement in App Development

Script issue with an If statement

Wed, 13 Jan 2016 16:51:12 GMT

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!

Re: Script issue with an If statement

sunny_talwar — Wed, 13 Jan 2016 17:35:56 GMT

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

];

Re: Script issue with an If statement

swuehl — Wed, 13 Jan 2016 17:36:27 GMT

MonthEnd(date [, shift = 0])

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]

Re: Script issue with an If statement

Thu, 14 Jan 2016 09:49:24 GMT

Thanks Both Floor did the trick. I will have to read up on that a bit more