Autogenerate dates and other data from resident table

[Jason_Michaelides]

Hi all,

I'm going to continue researching this but as yet haven't found an answer in any existing post so here's a new one - hope someone can help!

I have loaded some Advert data:

Adverts:
LOAD
AD_ID,
Site_ID.
AdStartDate,
AdEndDate,
AdTotalImpressions
FROM AllAds.csv;

Now I need to create a new table from this data which, for each AD_ID, generates a record for every day between AdStartDate and AdEndDate, together with an Impressions value of AdTotalImpressions divided by the number of days between AdStartDate and AdEndDate, inclusive.

For example:

AD_ID 10 starts on 01/02/2011 and ends on 28/02/2011 and has a total Impressions of 28,000. Site_ID=1
AD_ID 20 starts on 01/02/2011 and ends on 10/02/2011 and has a total Impressions of 20,000. Site_ID=2

I want 38 records in my new table - each with 4 fields (AD_ID, Site_ID, Date, Impressions):

The first 28 records should have AD_ID=10, Site_ID=1, Date=<28 values, 01-28/02/2011>, Impressions=1,000
The next 10 records should have AD_ID=20, Site_ID=2, Date=<10 values, 01-10/02/2011>, Impressions=2,000

Hope this is clear and you can help!

Thanks,

Jason

[Oleg_Troyansky · Accepted solution]

You need to use LOAD ... WHILE functionality (look it up in the manual). Something along those lines (don't guarantee the syntax):

LOAD

AD_ID,
Site_ID.
date(AdStartDate + IterNo() - 1 ) as Date,
AdTotalImpressions

RESIDENT Adverts

WHILE
AdStartDate + IterNo() - 1 <= AdEndDate
;

[Jason_Michaelides]

Hi Oleg,

Thank you very much - your answer confirmed I was on the right lines with code I found in another post, however I had been using RowNo() in my date creation rather than IterNo() which meant it wasn't resetting with each Ad_ID - a simple change and all is well!

Thanks again mate - much appreciated,

Jason