Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
We value your feedback – take our 5-minute QlikView modernization survey
cancel
Showing results for 
Search instead for 
Did you mean: 
hobanwashburne
Creator
Creator

Help with interval match

I've tried a couple of times and can't seem to get this to work.

I have attached a sample data set containing customer sales data by year. I want to use interval match to place each customer in a bucket according to FY16 sales, but I also want to see what the bucket was for FY15.

The final output should look something like:

Customer | FY16 Bucket | FY15 Bucket | 2016 | 2015 | 2014

1 Solution

Accepted Solutions
prieper
Master II
Master II

Hi Scott,

give it a try with the below script.

have used just one bucket, which is applied to all entries. If you need to apply different buckets, then the exercise should be made with parts of the fact-table, i.e. apply 2016-bucket to 2016-data, etc. Lateron the results may be concatenated again.

Load Buckets_FY16 and SalesData as before and add the below at the end

//    ==== Create a link betw values and intervals =================

    LinkBuckets:

        INTERVALMATCH (NetSales) LOAD from_FY16, to_FY16 RESIDENT Buckets_FY16;

//    ==== Get the Bucket-info into the link-table =================

    JOIN (LinkBuckets) LOAD * RESIDENT Buckets_FY16;

    DROP TABLE Buckets_FY16;            // no longer needed

//    ==== Merge Bucket-info with FactTable ========================      

    LEFT JOIN (SalesData) LOAD * RESIDENT Buckets;

    DROP TABLE Buckets;                    // no longer needed

    DROP FIELDS from_FY16, to_FY16;        // just for cleaning, not needed

HTH Peter

Edith says, it may make sense to add a sample qvw.

View solution in original post

4 Replies
prieper
Master II
Master II

... but the buckets are the same?

Peter

hobanwashburne
Creator
Creator
Author

But a customer could have changed buckets.

If customer X had a total net or 480,000 in 2015 and then 510,000 in 2016 that customer would have a FY15 Bucket of 250K to 500K and a FY16 Bucket of 500K to 1M

prieper
Master II
Master II

Hi Scott,

give it a try with the below script.

have used just one bucket, which is applied to all entries. If you need to apply different buckets, then the exercise should be made with parts of the fact-table, i.e. apply 2016-bucket to 2016-data, etc. Lateron the results may be concatenated again.

Load Buckets_FY16 and SalesData as before and add the below at the end

//    ==== Create a link betw values and intervals =================

    LinkBuckets:

        INTERVALMATCH (NetSales) LOAD from_FY16, to_FY16 RESIDENT Buckets_FY16;

//    ==== Get the Bucket-info into the link-table =================

    JOIN (LinkBuckets) LOAD * RESIDENT Buckets_FY16;

    DROP TABLE Buckets_FY16;            // no longer needed

//    ==== Merge Bucket-info with FactTable ========================      

    LEFT JOIN (SalesData) LOAD * RESIDENT Buckets;

    DROP TABLE Buckets;                    // no longer needed

    DROP FIELDS from_FY16, to_FY16;        // just for cleaning, not needed

HTH Peter

Edith says, it may make sense to add a sample qvw.

Not applicable

Hi

It's great your question has been answered - makes perfect sense as well!

I have written a white paper on interval match and the class function - hopefully it helps with any more use cases you may have or to enhance knowledge on this topic.

Thanks!