data subtraction calculation methos

[bushpalaamarnat]

Hi all,

Could any one please help.

Out put required is (week & material & Loc) week 6 - DFC Days should be subtracted from  (week & material & Loc) week 7  - DFC Days. the out put of this subtraction is in the new column. This way it should subtracted from week 8 - week 7, week 9- week 8 for 52 weeks.

Data Examples of input

Input
Week	Material	Loc	DFC Days
W06-18	1234	USD	4
W06-18	2345	IND	230
W06-18	3456	FR	450
W06-18	1234	UK	36
W06-18	9568	UK	41
W07-18	1234	USD	9
W07-18	2345	IND	237
W07-18	9568	UK	46
W06-18	3456	FR	455
W07-18	1234	UK	41
W08-18	9568	UK	51
W08-18	1234	USD	14
W08-18	2345	IND	244
W08-18	3456	FR	460
W08-18	1234	UK	46

Out put required is as follows

	Output
Week	Material	Loc	DFC Days	Difference in Days
W06-18	1234	USD	4	5
W06-18	2345	IND	230	7
W06-18	3456	FR	450	5
W06-18	1234	UK	36	5
W06-18	9568	UK	41	5
W07-18	1234	USD	9	5
W07-18	2345	IND	237	7
W07-18	9568	UK	46	5
W06-18	3456	FR	455	5
W07-18	1234	UK	41	5
W08-18	9568	UK	51	-51
W08-18	1234	USD	14	-14
W08-18	2345	IND	244	-244
W08-18	3456	FR	460	-460
W08-18	1234	UK	46	-46

[sunny_talwar · Accepted solution]

Try this

Table:

LOAD * INLINE [

    Week, Material, Loc, DFC Days

    W06-18, 1234, USD, 4

    W06-18, 2345, IND, 230

    W06-18, 3456, FR, 450

    W06-18, 1234, UK, 36

    W06-18, 9568, UK, 41

    W07-18, 1234, USD, 9

    W07-18, 2345, IND, 237

    W07-18, 9568, UK, 46

    W07-18, 3456, FR, 455

    W07-18, 1234, UK, 41

    W08-18, 9568, UK, 51

    W08-18, 1234, USD, 14

    W08-18, 2345, IND, 244

    W08-18, 3456, FR, 460

    W08-18, 1234, UK, 46

];

FinalTable:

LOAD *,

If(Material = Previous(Material) and Loc = Previous(Loc), Previous([DFC Days]) - [DFC Days], -[DFC Days]) as [Difference in Days]

Resident Table

Order By Material, Loc, Week desc;

DROP Table Table;

[bushpalaamarnat]

Thank it worked for me.