Find and summerize changes in data set

[Odelya]

Hi

This is my data

Contract	Status	AMOUNT	STATUS_DATE_From	STATUS_DATE_Next	Change Number
1001700	10	-287110.56	09/08/2017 00:00	20/08/2017 00:00	1
1001700	5	72320.75	20/08/2017 00:00	20/08/2017 00:00	2
1001700	50	72320.75	20/08/2017 00:00	30/08/2017 00:00	3
1001700	60	72320.75	30/08/2017 00:00	30/08/2017 00:00	4
1001700	90	72320.75	30/08/2017 00:00	24/09/2017 00:00	5
1001700	96	72320.75	24/09/2017 00:00	24/12/2017 00:00	6
1001700	10	72320.75	24/12/2017 00:00	20/08/2017 00:00	7
1001700	30	69285.18	20/08/2017 00:00	20/08/2017 00:00	8
1001700	50	69285.18	20/08/2017 00:00	30/08/2017 00:00	9
1001700	60	69285.18	30/08/2017 00:00	30/08/2017 00:00	10
1001700	90	69285.18	30/08/2017 00:00	24/09/2017 00:00	11
1001700	96	69285.18	24/09/2017 00:00	24/12/2017 00:00	12
1001700	98	69285.18	24/12/2017 00:00	24/12/2017 00:00	13
1001700	100	69285.18	24/12/2017 00:00	24/12/2017 00:00	14
1001700	90	69285.18	24/12/2017 00:00	24/12/2017 00:00	15
1001700	107	69285.18	24/12/2017 00:00	24/12/2017 00:00	16
1001700	110	69285.18	24/12/2017 00:00	24/12/2017 00:00	17
1001700	120	69285.18	24/12/2017 00:00	24/12/2017 00:00	18
1001700	130	69285.18	24/12/2017 00:00	25/12/2017 00:00	19
1001700	134	69285.18	25/12/2017 00:00	25/12/2017 00:00	20
1001700	136	69285.18	25/12/2017 00:00	25/12/2017 00:00	21
1001700	137	800000.00	25/12/2017 00:00	25/12/2017 00:00	22
1001700	139	800000.00	25/12/2017 00:00	26/12/2017 00:00	23
1001700	140	800000.00	26/12/2017 00:00	26/12/2017 00:00	24
1001700	141	69285.18	26/12/2017 00:00	04/01/2018 00:00	25
1001700	142	69285.18	04/01/2018 00:00	14/01/2018 00:00	26
1001700	160	69285.18	14/01/2018 00:00	01/01/2999 00:00	27

I need to present the amount of time there was a change in the contract information:

1. When there is a change for a lower status (change line number 2,7,15)
2. When there is an amount of change (lower or higher) (change line number 2,8,22,25)

I also need to summarize the number of times for each change listed above…

Any suggestions?

Thank you!

[sergio0592 · Accepted solution]

Hi,

In the preceding load of your load statement try to add:

LOAD *,
if(Status<Previous(Status),1,0) as FL_change,
if(rowno()>1 and AMOUNT<>Previous(AMOUNT),1,0) as FL_Amount

[Odelya]

Thank you!