Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello again,
I want connect two tables by adding key to second table.
Source tables look like that:
TAB1
KEY | ID | DATE_FROM | DATE_TO |
11311 | 1131 | 1970-08-19 | 2007-12-31 |
11312 | 1131 | 2008-01-07 | 2008-05-10 |
23851 | 2385 | 1996-01-02 | 2006-12-31 |
23852 | 2385 | 2008-07-16 | 2010-01-31 |
23853 | 2385 | 2018-11-30 |
TAB2
KEY2 | ID | DATE_FROM | DATE_TO |
1182495 | 1131 | 1973-02-16 | 1973-12-15 |
1182497 | 1131 | 1997-06-30 | 1997-06-30 |
1182515 | 1131 | 2000-09-05 | 2000-09-05 |
1301766 | 1131 | 2008-04-11 | 2008-04-11 |
1301849 | 1131 | 2008-05-05 | 2008-05-06 |
1302872 | 1131 | 2008-05-07 | 2008-05-09 |
841388 | 2385 | 2004-07-19 | 2004-07-19 |
1202499 | 2385 | 1997-01-02 | 1997-01-03 |
1202518 | 2385 | 1998-01-02 | 1998-01-02 |
1339364 | 2385 | 2009-10-12 | 2009-10-31 |
1339365 | 2385 | 2009-11-01 | 2009-11-27 |
1344561 | 2385 | 2010-01-04 | 2010-01-06 |
I need put value of key column to TAB2
When ID from TAB2 is EQ ID TAB1 and DATE_FROM and DATE_TO in TAB2 are between DATE_FROM and DATE_TO in TAB1, i put KEY into TAB2. Destination TAB2 should look like this:
KEY2 | KEY | ID | DATE_FROM | DATE_TO |
1182495 | 11311 | 1131 | 1973-02-16 | 1973-12-15 |
1182497 | 11311 | 1131 | 1997-06-30 | 1997-06-30 |
1182515 | 11311 | 1131 | 2000-09-05 | 2000-09-05 |
1301766 | 11312 | 1131 | 2008-04-11 | 2008-04-11 |
1301849 | 11312 | 1131 | 2008-05-05 | 2008-05-06 |
1302872 | 11312 | 1131 | 2008-05-07 | 2008-05-09 |
841388 | 23851 | 2385 | 2004-07-19 | 2004-07-19 |
1202499 | 23851 | 2385 | 1997-01-02 | 1997-01-03 |
1202518 | 23851 | 2385 | 1998-01-02 | 1998-01-02 |
1339364 | 23852 | 2385 | 2009-10-12 | 2009-10-31 |
1339365 | 23852 | 2385 | 2009-11-01 | 2009-11-27 |
1344561 | 23852 | 2385 | 2010-01-04 | 2010-01-06 |
Hi,
Try below script:
T1:LOAD * Inline
[
KEY,ID,DATE_FROM,DATE_TO
11311,1131,1970-08-19,2007-12-31
11312,1131,2008-01-07,2008-05-10
23851,2385,1996-01-02,2006-12-31
23852,2385,2008-07-16,2010-01-31
23853,2385,2018-11-30,
];
T2:LOAD * Inline
[
KEY2,ID,DATE_FROM,DATE_TO
1182495,1131,1973-02-16,1973-12-15
1182497,1131,1997-06-30,1997-06-30
1182515,1131,2000-09-05,2000-09-05
1301766,1131,2008-04-11,2008-04-11
1301849,1131,2008-05-05,2008-05-06
1302872,1131,2008-05-07,2008-05-09
841388,2385,2004-07-19,2004-07-19
1202499,2385,1997-01-02,1997-01-03
1202518,2385,1998-01-02,1998-01-02
1339364,2385,2009-10-12,2009-10-31
1339365,2385,2009-11-01,2009-11-27
1344561,2385,2010-01-04,2010-01-06
];
T3:LOAD
KEY,
ID,
Date(Date#(DATE_FROM,'YYYY-MM-DD'),'DD/MM/YYYY') as DATE_FROM1,
Date(Date#(DATE_TO,'YYYY-MM-DD'),'DD/MM/YYYY') as DATE_TO1
Resident T1;
Inner Join
LOAD
KEY2,
ID,
Date(Date#(DATE_FROM,'YYYY-MM-DD'),'DD/MM/YYYY') as DATE_FROM2,
Date(Date#(DATE_TO,'YYYY-MM-DD'),'DD/MM/YYYY') as DATE_TO2
Resident T2;
NoConcatenate
LOAD
KEY2,
ID,
DATE_FROM2,
DATE_TO2
Resident T3 Where DATE_FROM2>=DATE_FROM1 and DATE_TO2<=DATE_TO1;
DROP Tables T1,T2,T3;
Hi,
Try below script:
T1:LOAD * Inline
[
KEY,ID,DATE_FROM,DATE_TO
11311,1131,1970-08-19,2007-12-31
11312,1131,2008-01-07,2008-05-10
23851,2385,1996-01-02,2006-12-31
23852,2385,2008-07-16,2010-01-31
23853,2385,2018-11-30,
];
T2:LOAD * Inline
[
KEY2,ID,DATE_FROM,DATE_TO
1182495,1131,1973-02-16,1973-12-15
1182497,1131,1997-06-30,1997-06-30
1182515,1131,2000-09-05,2000-09-05
1301766,1131,2008-04-11,2008-04-11
1301849,1131,2008-05-05,2008-05-06
1302872,1131,2008-05-07,2008-05-09
841388,2385,2004-07-19,2004-07-19
1202499,2385,1997-01-02,1997-01-03
1202518,2385,1998-01-02,1998-01-02
1339364,2385,2009-10-12,2009-10-31
1339365,2385,2009-11-01,2009-11-27
1344561,2385,2010-01-04,2010-01-06
];
T3:LOAD
KEY,
ID,
Date(Date#(DATE_FROM,'YYYY-MM-DD'),'DD/MM/YYYY') as DATE_FROM1,
Date(Date#(DATE_TO,'YYYY-MM-DD'),'DD/MM/YYYY') as DATE_TO1
Resident T1;
Inner Join
LOAD
KEY2,
ID,
Date(Date#(DATE_FROM,'YYYY-MM-DD'),'DD/MM/YYYY') as DATE_FROM2,
Date(Date#(DATE_TO,'YYYY-MM-DD'),'DD/MM/YYYY') as DATE_TO2
Resident T2;
NoConcatenate
LOAD
KEY2,
ID,
DATE_FROM2,
DATE_TO2
Resident T3 Where DATE_FROM2>=DATE_FROM1 and DATE_TO2<=DATE_TO1;
DROP Tables T1,T2,T3;
Thank You for reply - need some time to check solution meanwhile got another problem... but this in another thread...
Regards
Everything works great (with small correction):
NoConcatenate
LOAD
KEY,
ID,
KEY2,
DATE_FROM2,
DATE_TO2
Great WORK !!!