adding specific date to the report

[#1]

Hi,

do you know if it is possible to create a field with date if i am not using any dates loading?

This is my data:

report     refreshment frequency          timing

1               weekly                            Mondays

2               monthly                           first Fridays of month

I would like to get the following output:

report     refreshment frequency          timing                              next refreshment date

1               weekly                            Mondays                              05.11.2012 (next Monday)

2               monthly                           first Fridays of month             02.11.2012 (first Friday in coming month - Nov)

Many thanks for help.

J.

[swuehl · Accepted solution]

Should be possible in principle.

You would need to define all timings and parse the field appropriately. For example, you can calculate the next Monday as of today like

=Date(today() + (7-WeekDay(today()) ) )

Then your script could look like

LOAD

report, [refreshment frequency], timing,

if( timing = 'Mondays', Date(today() + (7-WeekDay(today()) )), 'other calculations') as [next refreshment date]

FROM ...

but there are probably better / more elegant ways to pick the correct expression.

Hope this helps,

Stefan