topic Re: Interval Matching Dates With Multiple Keys in QlikView

Interval Matching Dates With Multiple Keys

Wed, 25 Mar 2015 19:01:57 GMT

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;

Re: Interval Matching Dates With Multiple Keys

hic — Wed, 25 Mar 2015 21:29:59 GMT

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;

Re: Interval Matching Dates With Multiple Keys

sudhanshu123 — Mon, 14 Jan 2019 18:54:37 GMT

Hi ,

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

Kindly Help!!

Re: Interval Matching Dates With Multiple Keys

AlexChirkin — Tue, 25 Feb 2020 12:50:55 GMT

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;

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