Thu Mar 22, 2018 5:33 pm
Login Register Lost Password? Contact Us

Creating new Dataset Records For Gaps

Questions around writing code and queries

Fri Jun 09, 2017 4:16 pm Change Time Zone


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.

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

Fri Jun 09, 2017 6:33 pm Change Time Zone


Here's how I would do that:
Code: Select all
  Std.Date.Days_t MyDate; //a "days since" date field
  UNSIGNED6 jid;
  STRING5 zip;

ds1 := DATASET([{Std.Date.FromJulianYMD(2017,1,1),1,30301},
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:
                  SELF.jid := IF(RIGHT.jid=0,LEFT.jid,RIGHT.jid),
         := IF('',,,
                  SELF := RIGHT));


Community Advisory Board Member
Community Advisory Board Member
Posts: 1341
Joined: Wed Oct 26, 2011 7:40 pm

Return to Programming

Who is online

Users browsing this forum: Bing [Bot] and 1 guest