Comparison of values and creating one output column

PB41091

Hi,

I have below kind of data set; where I need to compare Value column corresponding to combination of A, B and C.

For example, row 1 has a1,b1,10 and its value is 115 then I have to search for row where I have a1,b1,10 with different value and corresponding date. We need to do this search and compare values only for consecutive dates. Lets say in my dataset I have 3 dates - 22-Jun-23, 20-Sep-23 & 23-Oct-23. We need to compare only 22-Jun-23 with 20-Sep-23 and later 20-Sep-23 with 23-Oct-23 (and so on for all the available dates.)

Create the output column "comments" as shown in sample output data below. (row comparison if not printed in output is okay, this for you to understand how the comparison is been made)

Input data:

SN	A	B	C	Value	Date
1	a1	b1	10	115	22-Jun-23
2	a1	b2	20	120	22-Jun-23
3	a2	b3	30	110	22-Jun-23
4	a2	b4	40	145	22-Jun-23
5	a3	b2	20	125	22-Jun-23
6	a4	b3	30	150	22-Jun-23
7	a1	b1	10	120	20-Sep-23
8	a1	b2	20	120	20-Sep-23
9	a2	b3	30	110	20-Sep-23
10	a2	b4	40	140	20-Sep-23
11	a3	b2	20	105	20-Sep-23
12	a4	b3	30	130	20-Sep-23
13	a4	b3	30	120	23-Oct-23
14	a2	b4	40	145	23-Oct-23

Output data:

SN	A	B	C	Value	Date	Comments
1	a1	b1	10	115	22-Jun-23
2	a1	b2	20	120	22-Jun-23
3	a2	b3	30	110	22-Jun-23
4	a2	b4	40	145	22-Jun-23
5	a3	b2	20	125	22-Jun-23
6	a4	b3	30	150	22-Jun-23
7	a1	b1	10	120	20-Sep-23	Value change on 20-sep-23; comparison row 7 with row 1
8	a1	b2	20	120	20-Sep-23	No Change
9	a2	b3	30	110	20-Sep-23	No Change
10	a2	b4	40	140	20-Sep-23	Value change on 20-sep-23; comparison row 10 with row 4
11	a3	b2	20	105	20-Sep-23	Value change on 20-sep-23; comparison row 11 with row 5
12	a4	b3	30	130	20-Sep-23	Value change on 20-sep-23; comparison row 12 with row 6
13	a4	b3	30	120	23-Oct-23	Value change on 23-oct-23; comparison row 13 with row 12
14	a2	b4	40	145	23-Oct-23	Value change on 23-oct-23; comparison row 14 with row 10

ChrisTX

Why does this row get assigned a comment of "No Change"?

a2

b6

40

160

20-Sep-23

Are rows assigned a comment "Initial" based on the earliest Date in the data set?  But this one row has a comment "Initial" when the Date is not the earliest in the data set:

a1

b5

30

145

22-Oct-23

Why does a2 b6 40 not have a row for "Initial", and how should the logic assign a comment of "No Change", when the values of a2 b6 40 are first encountered?

Chris