Copy Previous rows data to next rows

[vikasshana]

Hi,

I've the below example data.

ID	Desc	Eq
1	ABC	123
1	DEF	123
1	GHI	123
1	JKL	123
1	MNO	123
1	PQR	123
2		123
3	APP	343
3	ASS	343
3	OPP	343
4		343

My requirement is to add what ever Desc's are there for ID '1' to '2' & '3' to '4', below is the expected output.

ID	Desc	Eq
1	ABC	123
1	DEF	123
1	GHI	123
1	JKL	123
1	MNO	123
1	PQR	123
2	ABC	123
2	DEF	123
2	GHI	123
2	JKL	123
2	MNO	123
2	PQR	123
3	APP	343
3	ASS	343
3	OPP	343
4	APP	343
4	ASS	343
4	OPP	343

Common value between those two rows are Eq.

I'm looking for a load script to achieve it. This is an example data, we have more records in real-time.

Regards,

V

[pravinboniface]

inp_table:
load * inline [
ID,Desc,Eq
1,ABC,123
1,DEF,123
1,GHI,123
1,JKL,123
1,MNO,123
1,PQR,123
2,,123
3,APP,343
3,ASS,343
3,OPP,343
4,,343
];

// Create new entries based on Eq
NewRows:
NoConcatenate
load ID,Eq
Resident inp_table
where len(Desc) = 0;

left join
load Desc,Eq
Resident inp_table
where len(Desc) > 0;

// Combine the rows we need
FinTable:
NoConcatenate
load * Resident inp_table
where len(Desc)>0;

Concatenate
load * Resident NewRows;

drop Table inp_table, NewRows;

exit Script;