Fri Aug 17, 2018 3:05 am
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

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

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

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
Community Advisory Board Member
Community Advisory Board Member
 
Posts: 1369
Joined: Wed Oct 26, 2011 7:40 pm


Return to Programming

Who is online

Users browsing this forum: No registered users and 1 guest

cron