Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

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;

Tags (3)
1 Solution

Accepted Solutions

Re: Interval Matching Dates With Multiple Keys

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;

2 Replies

Re: Interval Matching Dates With Multiple Keys

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;

Highlighted
Partner
Partner

Re: Interval Matching Dates With Multiple Keys

Hi ,

 

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

Kindly Help!!