topic Re: Help with interval match in QlikView

Help with interval match

hobanwashburne — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Help with interval match

prieper — Thu, 27 Apr 2017 15:35:04 GMT

... but the buckets are the same?

Peter

Re: Help with interval match

hobanwashburne — Thu, 27 Apr 2017 15:42:46 GMT

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

Re: Help with interval match

prieper — Fri, 28 Apr 2017 07:41:23 GMT

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.

Re: Help with interval match

Thu, 08 Jun 2017 07:59:34 GMT

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!