Loading Data/Script: Split daterange in single date items (lines)

[#1]

Hi everybody,

another question:

I have to load data via script:

LOAD

     Id,

     Website,

     Event,

     Startdate,

     Enddate

FROM

     ...

You can see that there is a start- and enddate for every event in this table.

Take a look at the table (example):

Id	Website	Event	Startdate	Enddate
1000	gulp.com	Product Roadshow	2011/05/03	2011/05/06
1001	heise.de	Release 0.9	2011/08/30	2011/09/02

I need to get ONE line for every single date. Is there a possibility to do this while running the import script (data reload script)?

I need a imported table in a structure like this (added a new column "Date"):

Id	Website	Event	Startdate	Enddate	Date
1000	gulp.com	Product Roadshow	2011/05/03	2011/05/06	2011/05/03
1000	gulp.com	Product Roadshow	2011/05/03	2011/05/06	2011/05/04
...	...	...	...	...	...
1000	gulp.com	Product Roadshow	2011/05/03	2011/05/06	2011/05/06
1001	heise.de	Release 0.9	2011/08/30	2011/09/02	2011/08/30
...	...	...	...	...	...
1001	heise.de	Release 0.9	2011/08/30	2011/09/02	2011/09/02

Thanks for every idea!

jup

[SunilChauhan]

use Intervalmatch

[gandalfgray · Accepted solution]

Hi

I would not recommend IntervalMatch.

Try this instead

// Setting upp some testdata

WebEvent:

LOAD * INLINE [

    Id, Website, Event, Startdate, Enddate

    1000, gulp.com, Product Roadshow, 2011/05/03, 2011/05/06

    1001, heise.de, Release 0.9, 2011/08/30, 2011/09/02

];

// Here is the actual code to use:

Left Join(WebEvent)

LOAD Id,

    Date(Startdate+iterno()-1) as Date

resident WebEvent

while iterno() <= num(Enddate)-num(Startdate)+1;

hth/gg

[#4]

Perfect - it works!

Great job!

Many thanks!

jup