Fri Dec 03, 2021 1:49 am
Login Register Lost Password? Contact Us


Joining Unrelated Datasets

Questions around writing code and queries

Tue Oct 12, 2021 2:46 pm Change Time Zone

Hello, I was wondering if in ECL we have an easy way of combining two datasets without any relationship between the datasets?

For example:

layoyt1 :=record
string phone
string first_seen
string last_seen
End
ds1:=table(infile1, layout1, phone, first_seen, last_seen);

layout2 := record
string buildversion;
end;
ds2:= table(infile2, layout2, buildversion);

What would be the easiest way to add buildversion if the date falls between first_seen and last_seen?

Output := record
string phone
string buildversion
string first_seen
string last_seen
End

Thanks for your help in advance.
mo0926
 
Posts: 18
Joined: Thu Jan 09, 2020 3:57 pm

Tue Oct 12, 2021 5:58 pm Change Time Zone

mo0926,
Hello, I was wondering if in ECL we have an easy way of combining two datasets without any relationship between the datasets?
The ONLY way to JOIN two datasets that actually have no relationship (in any language) would be to have a JOIN condition of TRUE. That would result in every record on the left matching every record on the right (i.e. if you have 10 recs on the left and 10 on the right you would end up with a 100 record result). Otherwise, you MUST have some data in both that can be used to match the records.
What would be the easiest way to add buildversion if the date falls between first_seen and last_seen?
Given this question, I have to assume that you have a date value that can be parsed from that buildversion string. Given that assumption, you could do it something like this:
Code: Select all
//I'm assuming:
// all string dates are in YYYYMMDD format
// a date is in characters 10 through 17 of the buildversion -- YMMV :)
IMPORT Std;
BOOLEAN IsInRange(Std.Date.Date_t d,
                  Std.Date.Date_t f,
                  Std.Date.Date_t l) :=
                    Std.Date.FromJulianDate(d) BETWEEN
                    Std.Date.FromJulianDate(f) AND
                    Std.Date.FromJulianDate(l);
JOIN(ds1,ds2,
     IsInRange((Std.Date.Date_t)RIGHT.buildversion[10..17],
               (Std.Date.Date_t)LEFT.first_seen,
               (Std.Date.Date_t)LEFT.last_seen),
     TRANSFORM(Output,SELF := LEFT,SELF := RIGHT));

HTH,

Richard
rtaylor
Community Advisory Board Member
Community Advisory Board Member
 
Posts: 1606
Joined: Wed Oct 26, 2011 7:40 pm


Return to Programming

Who is online

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