topic Re: Compare two dates in diffrent tables and return a single measure in QlikView

Compare two dates in diffrent tables and return a single measure

hbuchetmann — Sun, 15 May 2016 10:20:10 GMT

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.

Re: Compare two dates in diffrent tables and return a single measure

swuehl — Sun, 15 May 2016 17:38:17 GMT

Do you want to do the comparison in the script or the UI?

If you can do it in the script, you can look into

Creating a Date Interval from a Single Date

Creating Reference Dates for Intervals‌

IntervalMatch

IntervalMatch and Slowly Changing Dimensions

Re: Compare two dates in diffrent tables and return a single measure

Oleg_Troyansky — Sun, 15 May 2016 17:50:21 GMT

Hi Hermann,

I doubt if your solution can be called "a piece of cake", in fact it's quite time consuming. I'll give you a general direction...

You are dealing with the classical "slowly changing dimensions", and you should address the problem in your data model. The end goal is to assign the "valid purchase price" field to each record of Sales. Here are the steps:

1. In your pricing table, you have the "From" date, but not the "To" date. You will need the "To" date in order to link your prices to the Sales Dates. For that, reload your pricing table, sorted by Item and Date (Descending), and use the functions Peek() and Previous() to calculate the "To Date" as the previous value of the "From Date". When you sort by descending dates, the "previous" row really represents the "next" chronological record.

2. With both the "From" and the "To" dates known, use INTERVALMATCH to join the From and the Two dates by product to the Sales Date. You can also LEFT JOIN the Purchase Price field into your Sales.

After performing these two steps, you will have the accurate Purchase Price associated with each record of Sales.

If you are new to QlikView, and if you'd like to learn these and many other QlikView techniques, check out my recently published book QlikView Your Business - An expert Guide to QlikView and Qlik Sense.

Cheers,

Oleg Troyansky

Re: Compare two dates in diffrent tables and return a single measure

hbuchetmann — Sun, 15 May 2016 18:20:19 GMT

I was thinking about realising it in the UI, with a combination of set analysis and / or IF functionality, but script would be fine as well. At the moment i am a bit lost as how to continue, but thanks for the links, i will check them out and see if i can emerge a bit wiser.

Re: Compare two dates in diffrent tables and return a single measure

hbuchetmann — Sun, 15 May 2016 18:30:05 GMT

Hello Oleg,

thank you for your suggestions, although i fear they are a little over my skill level atm, i am not really sure what you mean with the "To" date.

Can you elaborte a little more?

Re: Compare two dates in diffrent tables and return a single measure

swuehl — Sun, 15 May 2016 18:46:16 GMT

Attached is an example following the previously referenced blog posts.

SET ThousandSep='.';
SET DecimalSep=',';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';
SET MoneyFormat='#.##0,00;-#.##0,00';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD.MM.YYYY';
SET TimestampFormat='DD.MM.YYYY hh:mm:ss[.fff]';
SET MonthNames='Jan;Feb;Mrz;Apr;Mai;Jun;Jul;Aug;Sep;Okt;Nov;Dez';
SET DayNames='Mo;Di;Mi;Do;Fr;Sa;So';
SET LongMonthNames='Januar;Februar;März;April;Mai;Juni;Juli;August;September;Oktober;November;Dezember';
SET LongDayNames='Montag;Dienstag;Mittwoch;Donnerstag;Freitag;Samstag;Sonntag';
SET FirstWeekDay=0;
SET BrokenWeeks=0;
SET ReferenceDay=4;
SET FirstMonthOfYear=1;
SET CollationLocale='de-DE';


Sales:
LOAD * INLINE [
    Auf_Nr, Pos, Art_Nr, Bezeichnung, Date_Delivery, Amount, Price_VK, Price_EK
    93495615, 1, 3681149, Grobblech S235JR (ST 37), 05.05.16, "2.703,00", "68,00", "52,30"
    93495672, 9, 3090750, Winkelstahl S235JR (ST37), 13.05.16, "496,00", "71,00", "54,03"
    93496007, 5, 3022040, Schnittkosten Kaltsäge, 10.04.16, "6,00", "6,50", "1,00"
    93495544, 3, 9302284, Stahlbauhohlprofil, 13.01.16, "6,01", "16,90", "10,15"
    93496002, 1, 3010140, Breitflanschträger S235JR (ST37), 13.05.16, "381,00", "63,00", "49,66"
];


Pricehistory:
LOAD * INLINE [
    Art_Nr, Date_Pricechange, Price
    3010140, 13.05.16, "53,09"
    3010140, 14.12.15, "49,66"
    3010140, 07.10.15, "53,82"
    3010140, 30.07.15, "54,27"
    3010140, 01.07.15, "53,64"
    3010140, 11.06.15, "53,67"
    3010140, 11.05.15, "53,67"
    3010140, 09.03.15, "53,91"
    3022040, 01.06.15, "5,85"
    3022040, 09.07.13, "0,00"
    3022040, 26.06.13, "0,00"
    3022040, 15.06.13, "0,00"
    3022040, 13.06.13, "0,00"
    3090750, 12.05.16, "59,99"
    3090750, 21.03.16, "54,03"
    3090750, 22.02.16, "61,00"
    3090750, 21.09.15, "62,00"
    3090750, 25.06.15, "62,29"
    3090750, 22.06.15, "123,12"
    3090750, 15.06.15, "62,46"
    3681149, 12.05.16, "56,00"
    3681149, 08.01.15, "52,30"
    3681149, 19.02.14, "57,81"
    3681149, 17.12.12, "60,00"
    3681149, 03.12.07, "68,67"
    3681149, 19.11.07, "69,15"
    3681149, 09.11.06, "67,07"
    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"
];


//version 1
TMP:
LOAD Art_Nr, Date_Pricechange as Datestart_Pricechange,
  Date(If(previous(Art_Nr)=Art_Nr,Previous(Date_Pricechange)-0.00000001,Today())) as Dateend_Pricechange
  ,Price
Resident Pricehistory
;


DROP TABLE Pricehistory;


JOIN 
IntervalMatch (Date_Delivery, Art_Nr)
LOAD Datestart_Pricechange,Dateend_Pricechange,Art_Nr
Resident TMP;


LEFT JOIN (Sales)
LOAD Date_Delivery, Price as History_Price
Resident TMP;


DROP TABLE TMP;


//version 2
//TMP:
//LEFT JOIN (Sales)
//LOAD Art_Nr, Date(Datestart_Pricechange+IterNo()-1) as Date_Delivery, Price as History_Price
//WHILE Datestart_Pricechange+IterNo()-1 <=Dateend_Pricechange;
//LOAD Art_Nr, Date_Pricechange as Datestart_Pricechange,
// Date(If(previous(Art_Nr)=Art_Nr,Previous(Date_Pricechange)-0.00000001,Today())) as Dateend_Pricechange
// ,Price
//Resident Pricehistory
//;
//
//DROP TABLE Pricehistory;

Auf_Nr	Pos	Art_Nr	Bezeichnung	Date_Delivery	Amount	Price_VK	Price_EK	History_Price
93496002	1	3010140	Breitflanschträger S235JR (ST37)	13.05.16	381,00	63,00	49,66	53,09
93496002	1	3010140	Breitflanschträger S235JR (ST37)	13.05.16	381,00	63,00	49,66	59,99
93496007	5	3022040	Schnittkosten Kaltsäge	10.04.16	6,00	6,50	1,00	5,85
93495672	9	3090750	Winkelstahl S235JR (ST37)	13.05.16	496,00	71,00	54,03	53,09
93495672	9	3090750	Winkelstahl S235JR (ST37)	13.05.16	496,00	71,00	54,03	59,99
93495615	1	3681149	Grobblech S235JR (ST 37)	05.05.16	2.703,00	68,00	52,30	52,30
93495544	3	9302284	Stahlbauhohlprofil	13.01.16	6,01	16,90	10,15	13,41

Re: Compare two dates in diffrent tables and return a single measure

hbuchetmann — Sun, 15 May 2016 19:06:35 GMT

Greetings again swuehl,

and many thanks for your example, i guess i start to understand in which direction the solution lies.

But when i look at the results, i get different history_price`s for the same combination of Au_Nr and Art_Nr.

There should only be one each. For example: the solution should return a history_Price of only 53,09 for Auf_Nr 93496002. In your example it returns two prices, and 59,99 is not even in the history price list for Art_Nr 3010140.

Re: Compare two dates in diffrent tables and return a single measure

hbuchetmann — Sun, 15 May 2016 19:16:32 GMT

I just tried your second version as well, this one returns the correct values!

Thanks a lot for the solution, although i need the couple of hours to really understand how you achiedved it exactly.

Would you have a solution to achieve the same result in the UI and not in the script, or is that not possible?

Re: Compare two dates in diffrent tables and return a single measure

swuehl — Sun, 15 May 2016 19:47:19 GMT

sorry, I forgot to add the Art_Nr key in the last JOIN:

TMP:

LOAD Art_Nr, Date_Pricechange as Datestart_Pricechange,

Date(If(previous(Art_Nr)=Art_Nr,Previous(Date_Pricechange)-0.00000001,Today())) as Dateend_Pricechange

,Price

Resident Pricehistory

;

DROP TABLE Pricehistory;

JOIN

IntervalMatch (Date_Delivery, Art_Nr)

LOAD Datestart_Pricechange,Dateend_Pricechange,Art_Nr

Resident TMP;

LEFT JOIN (Sales)

LOAD Art_Nr, Date_Delivery, Price as History_Price

Resident TMP;

DROP TABLE TMP;

Auf_Nr	Pos	Art_Nr	Bezeichnung	Date_Delivery	Amount	Price_VK	Price_EK	History_Price
93496002	1	3010140	Breitflanschträger S235JR (ST37)	13.05.16	381,00	63,00	49,66	53,09
93496007	5	3022040	Schnittkosten Kaltsäge	10.04.16	6,00	6,50	1,00	5,85
93495672	9	3090750	Winkelstahl S235JR (ST37)	13.05.16	496,00	71,00	54,03	59,99
93495615	1	3681149	Grobblech S235JR (ST 37)	05.05.16	2.703,00	68,00	52,30	52,30
93495544	3	9302284	Stahlbauhohlprofil	13.01.16	6,01	16,90	10,15	13,41

Re: Compare two dates in diffrent tables and return a single measure

swuehl — Sun, 15 May 2016 20:20:19 GMT

Hermann Buchetmann wrote:

I just tried your second version as well, this one returns the correct values!

Thanks a lot for the solution, although i need the couple of hours to really understand how you achiedved it exactly.

Would you have a solution to achieve the same result in the UI and not in the script, or is that not possible?

You can try this expression in the front end with your original data model:

=FirstSortedValue( Price, -If(Date_Delivery >= Date_Pricechange, Date_Pricechange))

Auf_Nr	Pos	Art_Nr	Bezeichnung	Date_Delivery	Amount	Price_EK	Price_VK	FirstSortedValue( Price, -If(Date_Delivery >= Date_Pricechange, Date_Pricechange))
								53,09
93496002	1	3010140	Breitflanschträger S235JR (ST37)	13.05.16	381,00	49,66	63,00	53,09
93496007	5	3022040	Schnittkosten Kaltsäge	10.04.16	6,00	1,00	6,50	5,85
93495672	9	3090750	Winkelstahl S235JR (ST37)	13.05.16	496,00	54,03	71,00	59,99
93495615	1	3681149	Grobblech S235JR (ST 37)	05.05.16	2.703,00	52,30	68,00	52,30
93495544	3	9302284	Stahlbauhohlprofil	13.01.16	6,01	10,15	16,90	13,41

Re: Compare two dates in diffrent tables and return a single measure

hbuchetmann — Sun, 15 May 2016 22:59:38 GMT

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?

Re: Compare two dates in diffrent tables and return a single measure

swuehl — Mon, 16 May 2016 08:33:06 GMT

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.

Re: Compare two dates in diffrent tables and return a single measure

hbuchetmann — Mon, 16 May 2016 18:50:31 GMT

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.