populate a new column in a table based on conditions on other columns

[noviceneil]

Hello Friends and Experts, 

                         I need some help on the following:

I have been trying to develop an app where I need to make different comparisons between different column values.

The input  table is as follows:

INPUT TABLE:
BOND_ID	purchase_date	matrutiy_date	VALUE	Type	prev_curr flag ( 1 =curr, 0 = prev)
1234	12/03/2020	31/12/2025	5000	EXT	1
1234	12/03/2020	31/12/2025	4800	INT	0
1112	13/03/2017	31/02/2020	66000	EXT	0
1114	13/03/2021	31/02/2020	70000	EXT	1

EXPECTED OUTPUT

:
BOND_ID	purchase_date	matrutiy_date	VALUE	Type	prev_curr flag ( 1 =curr, 0 = prev)	Reason
1234	12/03/2020	31/12/2025	5000	EXT	1	Type change
1234	12/03/2020	31/12/2025	4800	INT	0
1112	13/03/2017	31/02/2020	66000	EXT	0	BOND matured
1114	13/03/2021	31/02/2020	70000	EXT	1	Newly purchased

Here the BOND_ID is the key and prev_curr_flag is a column which is populated based on my previous period= 01st Dec, 2020 and current period=31st Mar, 2021.

Now, I need to make some comparison like,

1. as for first 2 rows BOND_ID are same for 2 different period, but Type is different then I need to highlight TYPE column for those 2 rows or mention in a separate column "REASON", i.e. the type has changed

2. for row 3, as the BOND_ID does not exist for current period and we can see that it's matured (maturity_date) before current period, hence need to highlight the maturity date column for this row or or mention in a separate column "REASON", i.e. the BOND has matured

2. for row 4, as the BOND_ID does not exist for previous period and we can see that it's purchased (purchase_date) after previous period, hence need to highlight the purchase date column for this row or mention in a separate column "REASON", i.e. the BOND is newly purchased.

I have attached an excel for the input table and expected result. Could you please help how this can be achieved? in case you have any other question please let me know.

[noviceneil]

I was trying the following,

after the  INPUT TABLE is loaded in put the script,

INPUT:

LOAD
BOND_ID,
purchase_date,
matrutiy_date,
contribution,
BOND_Type,
prev_curr_flag
FROM [lib://AttachedFiles/TEST_QS_INPUT.xlsx];

Left Join(INPUT)
LOAD
IF( ([BOND_ID]=PREVIOUS([BOND_ID]) ) and ([BOND_Type]<> PREVIOUS([BOND_Type])), 'TYPE CHANGED','') as REASON
RESIDENT INPUT
ORDER BY BOND_ID;

my output looks like the attached file where multiple rows get created extra.

Can anybody help me please?