topic Re: Re: Interval Match Problem in QlikView

Interval Match Problem

zagzebski — Thu, 09 Jan 2014 14:34:07 GMT

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...

Entity	ServiceDate	Ownership_Pct	Effective_YearMth	Expiration_YearMth
DEAN	200901	0.2	200801	999912
DEAN	200701	0.1	200701	200712
DNON	201001	0.6	200901	999912

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;

Re: Interval Match Problem

Thu, 09 Jan 2014 15:29:41 GMT

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 ;

Re: Interval Match Problem

hic — Thu, 09 Jan 2014 16:13:49 GMT

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:

HIC

Re: Interval Match Problem

zagzebski — Thu, 09 Jan 2014 16:15:24 GMT

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.

Re: Interval Match Problem

zagzebski — Thu, 09 Jan 2014 16:56:06 GMT

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

Re: Re: Interval Match Problem

hic — Thu, 09 Jan 2014 20:27:34 GMT

No problem. Check attached script.

HIC

Re: Re: Interval Match Problem

zagzebski — Thu, 09 Jan 2014 20:34:11 GMT

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

Re: Interval Match Problem

Thu, 09 Jan 2014 20:52:10 GMT

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;

Re: Interval Match Problem

hic — Thu, 09 Jan 2014 20:59:16 GMT

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

Re: Interval Match Problem

zagzebski — Thu, 09 Jan 2014 21:31:27 GMT

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). :

Entity	ServiceDate	Ownership_Pct	Effective_YearMth	Expiration_YearMth	Area
DEAN	200901	0.2	200801	999912	North
DEAN	200701	0.1	200701	200712	South
DNON	201001	0.6	200901	999912	East
DNON		0.5	200001	200812
TEST	201001				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;

Re: Interval Match Problem

hic — Thu, 09 Jan 2014 21:56:20 GMT

Yes. Change the last Join to

Right Join (OWNER_DEF)
Load * Resident FACT;

Re: Interval Match Problem

zagzebski — Thu, 09 Jan 2014 22:01:47 GMT

It works perfect! Thanks so much for sticking with me on this.