topic Joining two tables and WHERE in QlikView

Joining two tables and WHERE

gardan — Mon, 16 Aug 2010 15:56:20 GMT

I have two tables:

Table1:
Date, OriginID,DestID
2/1/2010,3345,USFF
3/5/2009,4453,USXX
2/23/2010,2123,USCC

Table2:
OriginID,Value,ValidOn,ValidTo
3345,dddd,12/31/2009,4/30/2010
3345,ffff,1/1/2009,12/30/2009
4453,dddd,12/31/2009,4/30/2010
4453,ffff,1/1/2009,12/30/2009
2123,dddd,12/31/2009,4/30/2010
2123,ffff,1/1/2009,12/30/2009

I want the end set look like this:
TableEND:
Date, OriginID,DestID,Value
2/1/2010,3345,USFF, dddd
3/5/2009,4453,USXX,ffff
2/23/2010,2123,USCC,dddd

I need make a WHERE statement where i look for Table1.Date field between Table2.ValidOn and Table2.ValidTo.

When I try to join them together then use a resident to filter out what i don't need, i run into memory issues. In reality Table1 and Table2 are pretty big. I tried to join them with a KEEP statement, but again, you can't really use the fields from table1 to filter on what you add in from table2

Joining two tables and WHERE

Mon, 16 Aug 2010 19:05:21 GMT

Something like this:


Table1:
load * inline [
Date, OriginID,DestID
2/1/2010,3345,USFF
3/5/2009,4453,USXX
2/23/2010,2123,USCC
];

Table2:
load * inline [
OriginID,Value,ValidOn,ValidTo
3345,dddd,12/31/2009,4/30/2010
3345,ffff,1/1/2009,12/30/2009
4453,dddd,12/31/2009,4/30/2010
4453,ffff,1/1/2009,12/30/2009
2123,dddd,12/31/2009,4/30/2010
2123,ffff,1/1/2009,12/30/2009
];

Left join (Table2)
intervalmatch (Date, OriginID)
load ValidOn, ValidTo, OriginID
resident Table2;

left join (Table1)
load OriginID, Date, Value
resident Table2;

drop table Table2;

Joining two tables and WHERE

gardan — Mon, 16 Aug 2010 19:16:17 GMT

Thanks for the help!