How to maintain Order and Product versions!!

[#1]

Hello Experts,

I have a scenario:

Say on Day1, a customer placed a order (Say Order No: 12345) for couple of products using our eCommerce site. The Order-form captures the "Order Number" and "Product Codes".

Now, say the customer ordered Product A (Price 100$), Product B (Price 150$) and Product C(Price 200$). In next couple of days, the details of Product A changed, the price changed from 100$ to 250$.

What is the best way to maintain the 'product' version to correctly identify the actual price of the product when the product was ordered.

By which I mean, order 12345 has three products A, B, C and prices respectively are 100, 150  & 200$. But if I check the price of the Product A from our record, it will now show 250$. But I need to map the actual price of the product at the time of the order. Please be reminded that the price of product can change couple of times in between.

Any food for thoughts. All these implementation are to be done in Qlikview side, as our ETL tool is really buggy and performing little funny these days.

Thanks in advance.

[Anonymous]

Hello,

You may solve this via a table like below:

Product      StartDate      EndDate      Price

A              01/01/2015   01/04/2015    100

A              02/04/2015    today()          150

B             15/02/2014   01/04/2015     200

B              02/04/2015    today()          250

C               15/02/2014    today()         100

So you have a pricelist table with proper dates etc.

You can join this pricelist table to Orders (via OrderDate and ProductKey) using Extended IntervalMatch. Use Help for that.

After a right intervalmatch it is possible to get the correct price at the relevant date of the order.

BR

Serhan 

[#3]

Hello Serhan,

I would try your idea. Now say, if other attributes of the product table also changes. So how can i in corporate and take into these changes using interval match.

[Anonymous]

Hi,

What else can change? Can you give an example?

With the same principle, you can work it out.

BR

Serhan

[beck_bakytbek]

Hi Bikash,

Try to make with help of crosstabel a table, where you can make with drill-down group for A,B,C as Dimension and you can show the price/products for each startdate and enddate.

with kind regards

Beck