topic Re: Re: Re: Check if invoiced prices matches price from Contract in QlikView

Check if invoiced prices matches price from Contract

Fri, 05 Dec 2014 09:15:34 GMT

Hi all

I am currently developing a Qlikview Based Purchase Analyzing Tool. One of the Tasks I have been given is to check if suppliers complies with the prices in the contract. 1-2 times a year in a 4 year contract period the suppliers have the right to regulate prices based on USD, index, etc. This gives me some challenges. A possible maximum of 8 pricelists.

A first simple overview of the thoughts I have done so far.

Pricelist (Table):

ContractID	SupplierID	ItemID	Startdate	Enddate	Price_pr_UOM	UOM
1000	12000000	DC-1000	01-01-2014	30-06-2014	1000	EA
1000	12000000	DC-1000	01-07-2014	31-12-2014	1250	EA
1000	12000000	DC-1000	01-01-2015	30-06-2015	1300	EA
1000	12000000	AC-2500	01-01-2012	31-12-2016	2500	EA

Invoice_Lines (Table):

InvoiceID	Invoice_Date	SupplierID	ItemID	InvoicedQTY	InvoicedAMT
1000	23-05-2014	12000000	DC-1000	2	1050
1100	15-07-2014	12000000	DC-1000	1	1250
1200	30-11-2013	12000000	DC-1000	1	1750
1300	15-04-2013	12000000	AC-2500	1	2600

I hope to be able to calculate the following:

total turnover on a contract (pr. item, pr. contract, pr. supplier)
overbilling pr. item, pr. supplier, pr. contract
total turnover outside of contract

with regards to different prices within the contract period.

My first thought would be to check during the load of invoice lines if the items bought is covered by an contract and a specific pricelist and enrich the Invoice_Lines (Table) with this information.

I will happily supply this thread with more information if needed.

Thank you

Anders

Re: Check if invoiced prices matches price from Contract

Fri, 05 Dec 2014 09:31:14 GMT

We expect around 4-10 million records in the Pricelist (Table) and around 2-4 million new records in Invoice_Lines (Table) pr. year.

Re: Check if invoiced prices matches price from Contract

Anonymous — Fri, 05 Dec 2014 10:37:30 GMT

Hi Anders,

You certainly have yourself an interesting problem there. I know that dealing with things like exchange rate variations is possible as we do that within Qlik but I am not certain of how it is handled. I believe this is an example of a slowly changing dimension. You might find some useful info in the following blog post.

http://community.qlik.com/blogs/qlikviewdesignblog/2013/06/03/slowly-changing-dimensions

Re: Check if invoiced prices matches price from Contract

Fri, 05 Dec 2014 11:55:18 GMT

Hi Adam

This is an really interesting input. Slowly Moving Dimensions.

I have read the blog now and try to use the principles on some sample data in the weekend and get back early next week.

Kind regards

Anders

Re: Check if invoiced prices matches price from Contract

Anonymous — Fri, 05 Dec 2014 14:16:19 GMT

Check out Intervalmatch and Intervalmatch(Extended) from QlikView Help.

You can join that table without multiplying it with the above method. Then you can do everything.

PS1: Well it multiplies it but in an efficient way.

Serhan

Re: Re: Check if invoiced prices matches price from Contract

Mon, 08 Dec 2014 12:25:00 GMT

Hi Serhan

I have attached my first test with the IntervalMatch.

It does almost what I want to achieve in very few steps, however every transaction not on a contract is discarded.

As I am a Qlikview novice, it is probably a newbie mistake.

Any suggestions?

Kind regards

Anders

Re: Re: Re: Check if invoiced prices matches price from Contract

Mon, 08 Dec 2014 14:23:28 GMT

I think I solved the problem above with a Left Join instead of inner join.

However Ending up with just one table dropping the Contracts and Bridging table might be a good idea as I have done in the attached document.

Bu this solution will of course change the no. of records in my transactions table, which doesnt seem like a good idea.

Another problem is that even though some Procurement specialists have told me that one itemID can only have one Price on a specific date, it only took 5 minutes to find an item present in different contracts with different prices. So I probably end up with having to deal with a Many-To-Many relationsship or simply only accept one record for an item in the Contracts on any date.

I just dont know how to make that check during dataload.

Re: Re: Re: Check if invoiced prices matches price from Contract

Anonymous — Mon, 08 Dec 2014 14:26:30 GMT

Hi Anders,

I can only write a quick note.

You dont have to inner join or left join. Just dont join and leave a synthetic key (a useful one for your case). That way you can keep 2 tables without cartesian.

Serhan

Re: Re: Re: Check if invoiced prices matches price from Contract

Thu, 11 Dec 2014 09:45:48 GMT

Thank you both for valid input to my challenges with Qlikview this time.

I solved my problem with the IntervalMatch and kept a synthetic key.

Kind regards

Anders