Script to move values from one row to another

[jduluc12]

Hi,

My data looks as follows:

ID	Note	HL	Type
1	COPP	H	P
1		L	P
2	FPTT	H	P
2		L	P
3	T566	H	S
3	T566	L	S

the value for the Note column is missing where

HL = L

and

Type = P

I want my data to look as follows:

ID	Note	HL	Type
1	COPP	H	P
1	COPP	L	P
2	FPTT	H	P
2	FPTT	L	P
3	T566	H	S
3	T566	L	S

It is basically copying the value of the Note field (COPP) from the row where

ID = 1 and HL = H and Type = P

to the row where

ID = 1 and HL = L and Type = P

and so on like other rows.

Thanks 

[mfchmielowski · Accepted solution]

Hi.

To achive this you can use previous() function. The key is to resident load the source table with specific order. For you it will be ID ASC, Note desc.

Something like

Noconcatenate Tmp:

Load ID, if(previous(ID) =ID and Note ='', previoud(Note), Note) as Note, HL, Type resident SourceTable order by ID ASC, Note desc;

Drop SourceTable; rename table Tmp to SourceTablr;