How to merge consecutive date ranges if the price remains the same

[midnight1]

Hi🙂

I have a table:

Tests:
Load
EAN, // barcodes
TYPE_OF_OFFER, // it has two type of values : 1000 which mean its a standard offer or anything other than 1000 which mean promotion
TYPE_OF_PRICE, // we are shop and it identifes if its a price of products we sell or the products we buy from delivers, it has values 1 or 2
START_DATE,
END_DATE,
PRICE
Resident Previous_table // i've changed names of variables
Order BY EAN, TYPE_OF_OFFER, TYPE_OF_PRICE, START_DATE;

EAN, TYPE_OF_OFFER, TYPE_OF_PRICE is my key, for those keys i want to find consecutive data ranges where the price remains and merge them.

I've marked with pink color ean's which are examples, the are consecutive date ranges for EAN&TYPE_OF_OFFER&TYPE_OF_PRICE and PRICE is the same.

i would like to merge those. And end up with start_date from first range and end_date from second range.

i dont know also how to take into consideration if there are three or four consecutive date ranges.

I tried something with 

EAN = Peek('EAN', -1)
AND TYPE_OF_OFFER= Peek('TYPE_OF_OFFER', -1 )
AND TYPE_OF_PRICE = Peek('TYPE_OF_PRICE ')
AND START_DATE = Peek('END_DATE', -1) + 1
AND PRICE = Peek('PRICE ', -1)

but i don't know what to do with it next 😓

[Oleg_Troyansky]

Hi there,

You are on the right track - you need to compare the key fields in your "current" row of data that's being loaded, with the previous row of data, and if the conditions are met, you should assign the previous version of the field "Start Date" to the current version of the same. This way you can "merge" several consecutive periods together.

You can find some similar examples in this blog post:

https://www.naturalsynergies.com/q-tip-12-peek-or-previous/

Good luck!