How to find the Difference between the two filed Values?

[Anonymous]

Hi All,

I have The following Data and from that,How to find out the Difference Between the SOHOty for the two weeks?

WareHouseType               ProductCode         Week                        SOHQty

SQL                                   1234                    20150103                    285

SQL                                    1234                   20150110                    315

[anbu1984]

Temp:
Load * Inline [
WareHouseType,ProductCode,Week,SOHQty
SQL,1234,20150103,285
SQL,1234,20150110,315
SQL,1234,20150117,500
SQL,123,20150103,285
SQL,123,20150110,315
SQL,123,20150117,500 ];


NoConcatenate
Final:
Load *,If(WareHouseType = Previous(WareHouseType) And ProductCode = Previous(ProductCode),
SOHQty - Previous(SOHQty)) As Diff Resident Temp Order by WareHouseType,ProductCode,Week;


Drop Table Temp;

[Anonymous]

Hi Anbu...

Thank you for the fast reply !!!

But for the First and fourth value in Diff filed getting Null values...But I need As same what is the value in SOHQty..

[anbu1984]

If(WareHouseType = Previous(WareHouseType) And ProductCode = Previous(ProductCode),
SOHQty - Previous(SOHQty),SOHQty)

[Anonymous · Accepted solution]

Small correction in your code.

Please have a Look..... 

Temp: 

Load * Inline [ 

WareHouseType,ProductCode,Week,SOHQty 

SQL,1234,20150103,285 

SQL,1234,20150110,315 

SQL,1234,20150117,500 

SQL,123,20150103,285 

SQL,123,20150110,315 

SQL,123,20150117,500 ]; 

NoConcatenate 

Final: 

Load *,

If(WareHouseType = Previous(WareHouseType) And ProductCode = Previous(ProductCode), 

SOHQty - Previous(SOHQty),SOHQty) As Diff Resident Temp Order by WareHouseType,ProductCode,Week; 

Drop Table Temp;

Thank You...

[Anonymous]

Thank You Anbu...