Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Not applicable

Joining two slowly changing tables

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!

1 Solution

Accepted Solutions

Re: Joining two slowly changing tables

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;

View solution in original post

2 Replies

Re: Joining two slowly changing tables

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;

View solution in original post

Not applicable

Re: Joining two slowly changing tables

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.