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

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

Mon, 29 Aug 2011 12:33:52 GMT

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 — Mon, 29 Aug 2011 12:46:58 GMT

use Intervalmatch

gandalfgray — Mon, 29 Aug 2011 12:51:17 GMT

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

Mon, 29 Aug 2011 15:03:18 GMT

Perfect - it works!

Great job!

Many thanks!

jup