Replace values

[nvijay5757]

Hi,

my data looks like

Modes	KD
8.0ANC	nKD
10.0AOR	KD
8.0AB	KD
8.0AB	nKD
12.0AB	KD
16.0AB	KD
22.0AB	nKD
25.0AB	nKD
25.0AB	nKD
35.0AB	nKD
35.0AB	KD
35.0AB	nKD

some Modes in KD column receiving two values like KD and nKD for that will replace to KD.

ex: 8.0AB have KD and nKD ->  nKD will replace to KD.   similarly,

35.0AB have KD and nKD ->  nKD will replace to KD.

help me thanks.

[PrashantSangle · Accepted solution]

try below code.

Table:
Load *, RecNo() as RecId Inline [
Modes, KD
8.0ANC, nKD
10.0AOR, KD
8.0AB, KD
8.0AB, nKD
12.0AB, KD
16.0AB, KD
22.0AB, nKD
25.0AB, nKD
25.0AB, nKD
35.0AB, nKD
35.0AB, KD
35.0AB, nKD
];
Left Join (Table)
LOAD
Modes,
FirstValue(KD) as KDNew
Resident Table
Group By Modes
Order By KD
;
Left Join (Table)
Load Modes,
count(Modes) as mode_cnt,
count( DISTINCT KD) as KD_cnt
Resident Table
Group by Modes;

NoConcatenate
Final:
Load RecId,Modes,KD,KDNew,if(mode_cnt>1 and KD_cnt >1 and KD='nKD',' ',KD) as KDNew1,mode_cnt,KD_cnt
Resident Table
;

Drop Tables Table;

Regards,

Prashant Sangle

[nvijay5757]

Hi, yes exactly.

like the input and output.

Input			Output
Modes	KD		Modes	KDnew
8.0ANC	nKD		8.0ANC	nKD
10.0AOR	KD		10.0AOR	KD
8.0AB	KD		8.0AB	KD
8.0AB	nKD		8.0AB
12.0AB	KD		12.0AB	KD
16.0AB	KD		16.0AB	KD
22.0AB	nKD		22.0AB	nKD
25.0AB	nKD		25.0AB	nKD
25.0AB	nKD		25.0AB	nKD
35.0AB	nKD		35.0AB
35.0AB	KD		35.0AB	KD
35.0AB	nKD		35.0AB

[PrashantSangle]

check my above solution