Calculation View - comparing two tables and taking only whats different

[Former Member]

Hello everybody!

I have two identical tables, one contains the stock from day - 2, the other contains the stock from day - 1.

*Day

*Stock

*Store

Quantity

I need to create a calculation view comparing the content of these two tables, and taking only the records from table "Day - 1" which are different from table "Day - 2".

What is the best way to do this using Calculation?

Thank you in advance!

Cris.

[Former Member · Accepted solution]

Hi,

Could you please tell the Table Definition and Sample data?

Thanks,

Pragati

[Former Member · Accepted solution]

The tables are as follows, the key fields contain the * symbol:

*Day

*Store

*Product

Quantity

Table D - 2

Day                      Store               Product               Quantity       

08/08/2015         StoreA                  MatA                    10

08/08/2015         StoreA                  MatB                    10

08/08/2015         StoreB                  MatA                    10

08/08/2015         StoreB                  MatB                    5

Table D-1

Day                      Store               Product               Quantity       

09/08/2015         StoreA                  MatA                    20

09/08/2015         StoreA                  MatB                    10

09/08/2015         StoreB                  MatA                    10

09/08/2015         StoreB                  MatC                    5

Result should be:

Store               Product               Quantity       

StoreA                  MatA                    20

StoreB                  MatC                    5

StoreB                  MatB                    0

Thanks!

[lbreddemann · Accepted solution]

Ok, this is a typical stock reporting requirement.

But it seems that the requirement is not complete.

What do you do when there is no stock entry for a specific product-location-combination for a specific date? I would assume you would like to have the "latest"/"most current" value then.

Or maybe, you'd say, if there wasn't an entry in there for more than x days, there must be something wrong and I need to mark this.

Also: why are the two views on the stock data separate? Isn't your stock kept in a single table?

[Former Member · Accepted solution]

In fact, this is not "that" typical.. It´s a special requirement, not an usual stock report.

There will be an initial load, with all stores and products. After that, I just need to send what has changed, it doesn't matter why it has changed. And I need to send the total in stock of what has changed,

That´s why I have two tables. This table contains 16 Million records for one day. I can't keep the history because of it's size. So the solution was to always keep D - 2 and D -1 , in order to compare what has changed between these two tables, and provide these changes in a third view.

[lbreddemann · Accepted solution]

So, if I understand correctly, then D-1 contains only yesterdays data and D-2 the data from the day before yesterday.

If that's true, one simple and efficient approach would be to perform a UNION of both tables, mapping the quantity information to a separate column each (with a constant 0 in the "missing" component).

Once this is done, an aggregation melts the records together and a calculated column can deliver the difference/delta value.

Let me know how you go with that.

[Former Member · Accepted solution]

Lars,

There is one problem... I don´t need the difference... If the data has changed, I need to send the quantity present on D-1...

Do you think it would be possible with your approach?

Thanks!!

Cris

[lbreddemann · Accepted solution]

In that case, don't compute the difference 🙂

To be honest, I only now understand that you want to filter out the records where no change happened and keep only those where there is a difference... *slow-thinking-today*...

I see the following approaches here:

1. You do a scripted calc. view and use "the power of SQL" to select all the records in TAB-1 that are not in TAB-2. The seldom used EXCEPT operator would be a good choice here.
   or
2. Instead of the difference, you calculate an indicator (changed? Yes/No) on which you filter

In fact I would probably do the whole thing in a plain SQL view anyhow - much less overhead from a design perspective and very expressive for this requirement.

So, my recommendation would be to do version 1.

- Lars

[lbreddemann · Accepted solution]

This would be an example code for this problem (on anyDB, but works in HANA the same way):

SQL Fiddle

Note that the requirement to print out

StoreB                  MatB                    0

is not fulfilled with that.

And to be honest, I don't really see the reasoning behind that,

StoreB/MatB did't change. It simply doesn't occur in D-1...

[Former Member · Accepted solution]

Thank you very much, Lars!!  This was really very helpfull and will definetely solve my problem!

Best Regards!

Cris

[raghavendragonuguntla · Accepted solution]

Hi Lars,

Any idea how tom implement the same (EXCEPT) logic in graphical calculation view?

It will be very much helpful.

Regards,

Raghavendra.