Compare two dates in diffrent tables and return a single measure

[hbuchetmann]

Hello everyone,

i am trying to find a solution to the following problem for quite some time now.(pls excuse my bad english from time to time, not my mother tongue)

I have two tables:

• Sales: shows which products sold on which date with amount, Sell and Buy Prices (the prices the salespersons entered in the contract) and the Number of the contract
• Pricehistory: shows the history of the buying prices of the products. In our business buying prices of our products constantly changes, and this table lists the date and the new price of a product. So everytime we buy a product and the price for that product changes, the system generates a new entry in this table.

Both tables are connected with the product number (Field Art_Nr).

I have extracted the most important fields with a few example values from out original table, you find them in the file EK Prüfung.qvw.

The values in the Sales Table show the prices our salespeople entred in the system on the day a product is sold.

Now i want to create functions which compares the buying price a salesperson entered in our IT - system (which fills table Sales) with the buying price which is acurate on the day of the sale.

Lets me explain for one example: the product with Art_Nr 9302284 has been sold on the 13.1.2016. The salesperson entered a buying price for that product on that day of 10,15 € (field Price_EK).

Auf_Nr	Pos	Art_Nr	Bezeichnung	Date_Delivery	Amount	Price_VK	Price_EK
93495544	3	9302284	Stahlbauhohlprofil	13.01.16	6,01	16,90	10,15

Now i check the pricehistory for that product: to find the correct price, i have to take the field Date_Delivery from talbe Sales and compare it with teh date field Date_Pricechange from table Pricehistory. I count down from the Date in Date_Delivery to the next lower date in Date-Pricechange in Pricehistory.

Art_Nr	Date_Pricechange	Price
9302284	12.05.16	14,08
9302284	21.04.16	10,15
9302284	13.07.15	13,41
9302284	16.07.14	21,53
9302284	22.05.14	15,38

So Date of the Sale ist Date_Delivery = 13.1.2016, the next lower date in Pricehistory is the 13.07.15.

So the correct price my function should return for Auf_Nr 93495544 and Art_Nr 9302284 is 13,41.

The new Table should contain all fields from Table Sales as Dimensions and a formula named validation_price, which returns for every entry in Table Sales the correct price on that day from table pricehistory (wich is always only one price or none).

I guess the key to the solution is to substract Date_Delivery from Date_Pricechange, then calculate the lowest positive number of that substration and from this return the corresponding field Price from Table Pricehistory, all packed up in a set analysis function.

But to be honest, that is overy my very basic skill level head, i am still a qlikview beginner.

I hope a more seasoned user looks at this and thinks "peace of cake" and returns a solutions here.

I really appreciate any help you guys can give me, i have uploaded a small example.

I tried out lots of solutions from this forum already, but nothing has yielded any results so far and i am getting desperate.

Thanks in advance for any help with my best wishes from german to all fellow qlikview users.

[hbuchetmann]

Ahh, that was exactly what i was looking for, thank you very much swuehl.

I already tested it with our data model and it works like a charm, only problem is performance ist a bit slow.

I guess i will try to integrate your version of the script as well.

To be honest i dont really understand how your solution with FirstSortedValue function works exactly, could you elaborate what it does in conjunction with your if clause?

[swuehl]

For each Auf_Nr, there are multiple records linked in the Pricehistory table by the key field value Art_Nr.

All these available records can be sorted ascending by the value of

-If(Date_Delivery >= Date_Pricechange, Date_Pricechange)

which will basicall return a (descending) list of Date_Pricechange values where the Date_Pricechange happened before or on Date_Delivery.

You can bring in then the related Price values to each Date_Pricechange and pick the first (now sorted) Price value  from the list.

That should be the Price value you are interested in, the valid Price for Date_Delivery.

[hbuchetmann]

Thanks for the explanation.

I also tried to implement the solution right into the scrip in the original datamodel, and after some tinkering it worked as well with much better performance.

Thanks again to Oleg for the general pointers and swuehl for the detailed solution.

I never would have been able to cook that up by myself.