Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Highlighted
chrisk44
Not applicable

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

 

 

2 Replies
amrinder
Not applicable

Re: Connecting two tables and put new key into second table

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;

 

 

chrisk44
Not applicable

Re: Connecting two tables and put new key into second table

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