Help with interval match

[hobanwashburne]

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

[prieper]

... but the buckets are the same?

Peter

[hobanwashburne]

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 · Accepted solution]

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.

[#5]

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!