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: 
zagzebski
Creator
Creator

Interval Match Problem

I have tried looking at all the discussions on this site but still can't get what I want! I keep getting results different that what I exopect and with a sythnetic table. Seems basic all I want is ONE table with the following results...

EntityServiceDateOwnership_PctEffective_YearMthExpiration_YearMth
DEAN2009010.2200801999912
DEAN2007010.1200701200712
DNON2010010.6200901999912

LOAD * INLINE [
Entity, Effective_YearMth, Expiration_YearMth, Ownership_Pct
DEAN, 200701, 200712,  0.10
DEAN, 200801, 999912,  0.20
DNON, 200001, 200812,  0.50
DNON, 200901, 999912,  0.60
]
;

FACT:
Load * Inline [
Entity, ServiceDate
DEAN, 200901
DEAN, 200701
DNON, 201001]
;

Left Join(FACT)
IntervalMatch([ServiceDate],Entity)

Load
Effective_YearMth,
Expiration_YearMth,
Entity
Resident OWNER_DEF;

1 Solution

Accepted Solutions
hic
Former Employee
Former Employee

Yes. Change the last Join to

Right Join (OWNER_DEF)
Load * Resident FACT;

View solution in original post

11 Replies
Not applicable

I believe, In your requirement, IntervelMatch won't fix the problem because both tables having Entity.

Please try code like below:

TEMP:

LOAD * INLINE [

Entity, Effective_YearMth, Expiration_YearMth, Ownership_Pct

DEAN, 200701, 200712,  0.10

DEAN, 200801, 999912,  0.20

DNON, 200001, 200812,  0.50

DNON, 200901, 999912,  0.60

];

Left Join

Load * Inline [

Entity, ServiceDate

DEAN, 200901

DEAN, 200701

DNON, 201001];

FINAL:

LOAD * Where FLAG;

LOAD * , IF(ServiceDate >= Effective_YearMth AND ServiceDate <= Expiration_YearMth , 1,0) AS FLAG

Resident TEMP;

DROP Table TEMP;

DROP Field FLAG ;

hic
Former Employee
Former Employee

I would not join the tables - but you can if you know that there is a one-to-one relationship between the dates and the intervals. I would instead go for the following model:

Data model.png

HIC

zagzebski
Creator
Creator
Author

Thanks for the response. Unfortunately I am not sure this will work. In my FACT table (from above) there is a chance there will be a record that does not join to the Owner Def table so it will get dropped because it is on the right side of the left join.

zagzebski
Creator
Creator
Author


Henric -

Thanks for the response. Is there any way to just get all the data into the fact table. and not have to deal with a bridge table. we have quite a large model and I am hoping just to bring all the data into the FACT table.

Steve

hic
Former Employee
Former Employee

No problem. Check attached script.

HIC

zagzebski
Creator
Creator
Author

Thanks again.

Getting close. However the resulting table is the Owner Def table. I would lose my FACT table - which is the main table in our model. The Owner Def table is just an attributes table that can/should go away after using it in the interval match..

Any thoughts?

Steve

Not applicable

Just change the Join to FACT table like below:

OWNER_DEF:

LOAD * INLINE [

Entity, Effective_YearMth, Expiration_YearMth, Ownership_Pct

DEAN, 200701, 200712,  0.10

DEAN, 200801, 999912,  0.20

DNON, 200001, 200812,  0.50

DNON, 200901, 999912,  0.60

];

FACT:

Load * Inline [

Entity, ServiceDate

DEAN, 200901

DEAN, 200701

DNON, 201001];

Join (FACT)

IntervalMatch([ServiceDate],Entity)

Load

Effective_YearMth,

Expiration_YearMth,

Entity

Resident OWNER_DEF;

Left Join (FACT) // Left Join Keep only all records in FACT Tabel

Load * Resident OWNER_DEF;

Drop Table OWNER_DEF;

hic
Former Employee
Former Employee

No, the table is the join between the tables, so it contains all information from both tables. Yes, it is labeled OWNER_DEF, but this is just a matter of labeling. To change the join order - so that you start with the joining to the FACT table - does not change anything.

HIC

zagzebski
Creator
Creator
Author

Okay I think I have it finally! My last question is that an extra row is created in the resulting table (bolded record with DNON and no service date or area). This record was not from the FACT table  but got created through the join process. Any way to only have the FACT table records records?

The resulting table is (updated the code below slighlty). :

EntityServiceDateOwnership_PctEffective_YearMthExpiration_YearMthArea
DEAN2009010.2200801999912North
DEAN2007010.1200701200712South
DNON2010010.6200901999912East
DNON 0.5200001200812
TEST201001 West

LOAD * INLINE [
Entity, Effective_YearMth, Expiration_YearMth, Ownership_Pct
DEAN, 200701, 200712,  0.10
DEAN, 200801, 999912,  0.20
DNON, 200001, 200812,  0.50
DNON, 200901, 999912,  0.60
]
;

FACT:
Load * Inline [
Entity, ServiceDate, Area
DEAN, 200901, North
DEAN, 200701, South
DNON, 201001, East
TEST, 201001, West]
;

Join (OWNER_DEF)
IntervalMatch([ServiceDate],Entity)

Load
Effective_YearMth,
Expiration_YearMth,
Entity
Resident OWNER_DEF;

Join (OWNER_DEF)
Load * Resident FACT;

Drop Table FACT;