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 (QVW attached)

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_FactProductEntity_FactEntityEffective_YearMthExpiration_YearMthServiceDateOwnership_Pct
SMDVSMDVDEANDEAN2007012007122007010.1
SMDVSMDVDEANDEAN2008019999122009010.2
SNONSNONDEANDEAN2008012013122010010.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

1 Solution

Accepted Solutions
Anonymous
Not applicable

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

View solution in original post

3 Replies
Anonymous
Not applicable

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

zagzebski
Creator
Creator
Author

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.

zagzebski
Creator
Creator
Author

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