Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Interval Matching Dates With Multiple Keys

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;

1 Solution

Accepted Solutions
hic
Former Employee
Former Employee

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;

View solution in original post

3 Replies
hic
Former Employee
Former Employee

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
Partner - Contributor III
Partner - Contributor III

Hi ,

 

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

Kindly Help!!

 

AlexChirkin
Contributor II
Contributor II

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;