Skip to main content

Qlik

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
chrisk44
Contributor III
Contributor III
‎2019-03-14 06:28 PM

Connecting two tables and put new key into second table

Hello again, 

I want connect two tables by adding key to  second table.

Source tables look like that:

TAB1

KEYIDDATE_FROMDATE_TO
1131111311970-08-192007-12-31
1131211312008-01-072008-05-10
2385123851996-01-022006-12-31
2385223852008-07-162010-01-31
2385323852018-11-30 

 

TAB2

KEY2IDDATE_FROMDATE_TO
118249511311973-02-161973-12-15
118249711311997-06-301997-06-30
118251511312000-09-052000-09-05
130176611312008-04-112008-04-11
130184911312008-05-052008-05-06
130287211312008-05-072008-05-09
84138823852004-07-192004-07-19
120249923851997-01-021997-01-03
120251823851998-01-021998-01-02
133936423852009-10-122009-10-31
133936523852009-11-012009-11-27
134456123852010-01-042010-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:

KEY2KEYIDDATE_FROMDATE_TO
11824951131111311973-02-161973-12-15
11824971131111311997-06-301997-06-30
11825151131111312000-09-052000-09-05
13017661131211312008-04-112008-04-11
13018491131211312008-05-052008-05-06
13028721131211312008-05-072008-05-09
8413882385123852004-07-192004-07-19
12024992385123851997-01-021997-01-03
12025182385123851998-01-021998-01-02
13393642385223852009-10-122009-10-31
13393652385223852009-11-012009-11-27
13445612385223852010-01-042010-01-06

 

 

453 Views
0 Likes
1 Solution

Accepted Solutions
amrinder
Creator
Creator
‎2019-03-15 03:13 AM

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;

 

 

View solution in original post

436 Views
0 Likes
3 Replies
amrinder
Creator
Creator
‎2019-03-15 03:13 AM

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;

 

 

437 Views
0 Likes
chrisk44
Contributor III
Contributor III
‎2019-03-16 06:25 PM
Author

In response to amrinder

Thank You for reply - need some time to check solution meanwhile got another problem... but this in another thread...
Regards

426 Views
0 Likes
chrisk44
Contributor III
Contributor III
‎2019-03-23 07:19 AM
Author

In response to amrinder

Everything works great (with small correction):

NoConcatenate
LOAD
KEY,
ID,
KEY2,
DATE_FROM2,
DATE_TO2

 

Great WORK !!!

 

390 Views