topic Re: Interval Match problem (QVW attached) in QlikView

Interval Match problem (QVW attached)

zagzebski — Wed, 27 Nov 2013 20:04:45 GMT

I have two problems with the example Interval Match (attached):

1. How do I add the additional requirements of needing to join:

a. Entity to the Entity_Fact fields

b Product and the Product_Fact

Ideally the table would look like this since only three records...as the entitys would have to match, the products would have to match and the service date would have to fall between the Effective and Expiration dates

Product_Fact	Product	Entity_Fact	Entity	Effective_YearMth	Expiration_YearMth	ServiceDate	Ownership_Pct
SMDV	SMDV	DEAN	DEAN	200701	200712	200701	0.1
SMDV	SMDV	DEAN	DEAN	200801	999912	200901	0.2
SNON	SNON	DEAN	DEAN	200801	201312	201001	0.3

2. Secondly, once that works I just want to add the Ownership_Pct column to the FACT table. And drop all the other tables.

Any help is GREATLY appreciated...

Steve

Re: Interval Match problem (QVW attached)

Anonymous — Wed, 27 Nov 2013 20:28:04 GMT

Hi Steve,

Take a look at the advanced interval match description in the help file. It explains how to do the very thing that you're talking about.

To do what you're looking for, try the following:

OWNER_DEF:

LOAD * INLINE [

    Entity, Product, Effective_YearMth, Expiration_YearMth, Ownership_Pct

    DEAN, SMDV, 200701, 200712, 0.10

    DEAN, SMDV, 200801, 999912, 0.20

    DEAN, SNON, 200801, 201312, 0.30

    DEAN, SNON, 200401, 999912, 0.40

    DNON, SMDV, 200001, 999912, 0.50

    DEAN, DHPP, 200001, 999912, 0.60

];

FACT:

Load * Inline [

ServiceDate, Entity, Product

200901, DEAN, SMDV

200701, DEAN, SMDV

201001, DEAN, SNON];

Inner Join(OWNER_DEF)

IntervalMatch([ServiceDate], Entity, Product)

Load

Effective_YearMth,

Expiration_YearMth,

Entity,

Product

Resident

OWNER_DEF

;

Left Join(FACT)

LOAD

Entity,

Product,

ServiceDate,

Ownership_Pct

Resident

OWNER_DEF

;

DROP Table

OWNER_DEF

;

Notice the extended syntax in the interval match. The first field listed is the "between" value and the last two are other keys to join against. The fields listed in the load statement have to match the ones in the interval statement. You can alias them to match, but it's actually easier if they are already named the same.

This actually produces 4 rows since your intervals for {Entity:DEAN, Product:SNON} overlap.

Hope this helps.

Jeff

Re: Interval Match problem (QVW attached)

zagzebski — Mon, 02 Dec 2013 15:02:04 GMT

Jeff -

This helped alot!

I only have one problem. I was hoping the left join would allow me to add fields to the FACT table (ie Ownership_Pct).

However when I drop the OWNER_DEF table I don't have the Ownership_Pct field.

Basically I want only one table in the end: the FACT but with the Ownership_Pct field joined in.

Re: Interval Match problem (QVW attached)

zagzebski — Mon, 02 Dec 2013 15:06:50 GMT

Sorry - I add a small error. Forget my prvious reply. It works perfect!