Interval Matching Dates With Multiple Keys

[#1]

I Have been having trouble with interval matching. I have two tables. One that has the Date intervals call it IntervalTable,

and a second table with the dates call it FactTable. Both tables have a key in common and the FactTable also has another field that is just a counter (could be something else).

I just made up some tables to illustrate the problem. The FinalTable is what I want. When I try I either lose some data or my track field is wrong.

IntervalTable:

FactTable:

FinalResult:

What I have tried so far is.

IntervalTable:

Load Field, Start, End, Fruit

From [];

FactTable:

Load Date, Field, Counter

From[];

Intervalmatch(Date, Field) Left Join

Load Start, End, Field

Resident IntervalTable;

Left Join (FactTable)

Load *

Resident IntervalTable;

Drop Table IntervalTable;

[hic · Accepted solution]

Try the following. See also Slowly Changing Dimensions

/HIC

IntervalTable:

Load Field, Start, End, Fruit,

   Field & '|' & Start & '|' & End as IntervalKey

From [];

FactTable:

Load Date, Field, Counter,

   Field & '|' & Date as DateKey

From[];

tmpBridge:

Intervalmatch(Date, Field)

Load Start, End, Field

Resident IntervalTable;

Bridge:

Load

   Field & '|' & Start & '|' & End as IntervalKey,

   Field & '|' & Date as DateKey

Resident tmpBridge;

Drop Table tmpBridge;

Drop Field Field From FactTable;

[sudhanshu123]

Hi ,

How can we get this data in our fact table.If We require to end up in one table.

Kindly Help!!

[AlexChirkin]

Hi, Henric!

Could you help me please?

In my case only difference to example above is I have 3 key fields in tables.  I did exactly as you advised. But there is one problem.  Field 'counter' doesn't connect to the rest data model. If i drop field 'field' from IntervalTable, field 'Fruit' doesn't connect to the rest table.

And one more moment I can't understand: in Table Viewer  table Bridge doesn't have any rows.

My script:

FactTable:
LOAD 
field1 & '|' & field2 & '|' & field3 & '|' & Date as %DateKey,
Date, field1, field2, field3, counter
FROM [] (qvd);

IntervalTable:
LOAD 
    field1 & '|' & field2 & '|' & field3 & '|' & Start  & '|' &  End  as %IntervalKey ,
    Start,End, field1, field2, field3, Fruit
FROM [] (qvd);

tmpBridge:
IntervalMatch(Date, field1, field2, field3)
LOAD 
Start, End, field1, field2, field3
Resident IntervalTable;

Bridge:
LOAD
field1 & '|' & field2 & '|' & field3 & '|' & Start & '|' & End as %IntervalKey,
field1 & '|' & field2 & '|' & field3 & '|' & Date as %DateKey
Resident tmpBridge;

DROP Table tmpBridge;
DROP fields field1, field2, field3 from FactTable;