Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
zagzebski
Contributor

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

Tags (2)
1 Solution

Accepted Solutions
jdvermeire
Contributor

Re: Interval Match problem (QVW attached)

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 {EntitySmiley Very HappyEAN, ProductSmiley FrustratedNON} overlap.

Hope this helps.

Jeff

3 Replies
jdvermeire
Contributor

Re: Interval Match problem (QVW attached)

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 {EntitySmiley Very HappyEAN, ProductSmiley FrustratedNON} overlap.

Hope this helps.

Jeff

zagzebski
Contributor

Re: Interval Match problem (QVW attached)

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
Contributor

Re: Interval Match problem (QVW attached)

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

Community Browser