Mon Aug 20, 2018 8:00 pm

## Creating new Dataset Records For Gaps

Questions around writing code and queries
Hello,

Suppose I have 3 records, dated 1/1, 1/4 and 1/6. Records are missing for 1/2, 1/3 and 1/5
So the dataset begins as:
1/1 jid=1 zip=30301
1/4 jid=2 zip= 30304
1/6 jid=3 zip=30306

I want to duplicate the record twice for 1/1 and put a date upon it as 1/2 and 1/3. I also want to duplicate the record for 1/4 and put a date of 1/5 on it. I want to be able to do this dynamically. I want to somehow compare the two adjacent records and create the appropriate number of records so the final dataset will look like:
1/1 jid=1 zip=30301
1/2 jid=1 zip=30301
1/3 jid=1 zip=30301
1/4 jid=2 zip=30304
1/5 jid=2 zip=30304
1/6 jid=3 zip=30306

I could go through the first time and subtract the dates from 1/1 and 1/4 and get 3 and subtract 1 from it. Same with 1/4 and 1/6. Then I would have:
1/1 2
1/4 1

Then use this number to do a normalize for that number the next time through with an appropriate transform but that seems very clunky. I am sure you have a better suggestion.

Thanks.
georgeb2d

Posts: 93
Joined: Wed Dec 24, 2014 3:36 pm

georgeb2d,

Here's how I would do that:
Code: Select all
`IMPORT Std;Rec := RECORD  Std.Date.Days_t MyDate; //a "days since" date field  UNSIGNED6 jid;  STRING5 zip;END;ds1 := DATASET([{Std.Date.FromJulianYMD(2017,1,1),1,30301},                {Std.Date.FromJulianYMD(2017,1,4),2,30304},                {Std.Date.FromJulianYMD(2017,1,6),3,30306}],Rec);MinDate   := MIN(ds1,MyDate);DateRange := MAX(ds1,MyDate) - MinDate + 1;//first create a new dataset with all the dates in the range:ds2 := DATASET(DateRange,               TRANSFORM(Rec,SELF.MyDate := MinDate + COUNTER - 1, SELF := []));//then LEFT OUTER JOIN that to ds1 to pick up the data from ds1:ds3 := JOIN(ds2,ds1,LEFT.MyDate=RIGHT.MyDate,            TRANSFORM(rec,SELF.MyDate := LEFT.MyDate, SELF := RIGHT),                  LEFT OUTER);//then ITERATE through that to do the "fill in" phase:ITERATE(SORT(ds3,MyDate),        TRANSFORM(Rec,                  SELF.jid := IF(RIGHT.jid=0,LEFT.jid,RIGHT.jid),                  SELF.zip := IF(RIGHT.zip='',LEFT.zip,RIGHT.zip),                  SELF := RIGHT));`

HTH,

Richard
rtaylor