topic Re: Joining two slowly changing tables in QlikView

Joining two slowly changing tables

Wed, 20 Feb 2013 15:36:05 GMT

Hello all -

I have two tables that are slowly chaging, but with a common OK. I need to join them. I've seen the IntervalMatch examples, but I'm not sure how to manage it with two slowly changing tables instead of one.

Example:

TableA:

SystemID, Status, Start, End

1, Active, 1/1/2011, 5/31/2011

1, Closed, 6/1/2011, 10/31/2011

1, Active, 11/1/2011, 2/31/2013

2, Active, 1/1/2012, 12/31/2012

2, Closed, 1/1/2013. 2/31/2013

TableB:

SystemID, Territory, Start, End

1, A, 1/1/2011, 12/31/2011

1, B, 1/1/2012, 2/31/2013

2, C, 1/1/2012, 5/31/2012

2, B, 6/1/2012, 2/31/2013

The Resulting table should be:

Final:

System ID, Status, Territory, Start, End

1, Active, A, 1/1/2011, 5/31/2011

1, Closed, A, 6/1/2011, 10/31/2011

1, Active, A, 11/1/2011, 12/31/2011

1, Active, B, 1/1/2012, 12/31/2011

2, Active,C, 1/1/2012, 5/31/2012

2, Active,B, /61/2012, 12/31/2012

2, Closed,B, 1/1/2013. 2/31/2013

Thanks!

Re: Joining two slowly changing tables

hic — Wed, 20 Feb 2013 15:57:17 GMT

I would use a While loop to create the dates in the interval. But it can be done with intervalmatch also.

The solution contains three synthetic keys, but in this case they are correct and - in fact the most efficient solution. If you want you can probably change the script to manually created composite keys instead.

/HIC

TableA:

Load SystemID as SystemIDA,

Status,

Date#(Start) as StartA,

Date#(End) as EndA

inline

[SystemID, Status, Start, End

1, Active, 1/1/2011, 5/31/2011

1, Closed, 6/1/2011, 10/31/2011

1, Active, 11/1/2011, 2/31/2013

2, Active, 1/1/2012, 12/31/2012

2, Closed, 1/1/2013, 2/31/2013];

TableB:

Load SystemID as SystemIDB,

Territory,

Date#(Start) as StartB,

Date#(End) as EndB

inline

[SystemID, Territory, Start, End

1, A, 1/1/2011, 12/31/2011

1, B, 1/1/2012, 2/31/2013

2, C, 1/1/2012, 5/31/2012

2, B, 6/1/2012, 2/31/2013];

IntervalMatchA:

Load SystemIDA as SystemID, StartA, EndA,

Date;

Load *,

Date(StartA + IterNo() - 1) as Date

Resident TableA

While IterNo() <= EndA - StartA + 1;

IntervalMatchB:

Load SystemIDB as SystemID, StartB, EndB,

Date;

Load *,

Date(StartB + IterNo() - 1) as Date

Resident TableB

While IterNo() <= EndB - StartB + 1;

Re: Joining two slowly changing tables

Wed, 20 Feb 2013 17:22:58 GMT

Thanks! This get's me what need, I can then load a table from them and drop the old ones. I searched around, but all of the examples were fact to slowly changing dimension.

Strangely, I have a couple of extra days in the Date field that aren't in the source - 3/1/2013-3/3/2013, but I'll figure that out.