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.

[GregAtStretch]

There would be several ways to do this, but you could use the replace() function in your load script.

[JordyWegman]

Hi,

Try this. I use the sorting so we can do the previous to check the value. And then filter out the values that are duplicate. Then I also load distinct so duplicate values are removed from the source (like 35.0AB with nKD).

Table:
Load * 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
];

NoConcatenate
Final:
Load Distinct
	Modes,
    KD
Where _indFilter <> 1
;
Load
	*,
	IF( Modes & 'n' & KD  = Previous( Modes & KD )
    	, 1
        , 0
	) as _indFilter
;
Load
	*
Resident Table
Order by Modes desc, KD desc
; 

Drop table Table;

Exit script

Jordy

Climber

[rwunderlich]

If I understand correctly you want to replace all occurrences of "nKd" with "KD". Insert this script before you load the table. 

KDMap:
Mapping Load * Inline [
from, to
nKD, KD
]
;
Map KD using KDMap; 

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

[nvijay5757]

Hi,

Thanks for your reply.

sorry to misunderstand not to replace all occurrences, 

my input and output looks like,

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	KD
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	KD
35.0AB	KD		35.0AB	KD
35.0AB	nKD		35.0AB	KD

[rwunderlich · Accepted solution]

Now I understand. So something like this?

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
; 

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

[nvijay5757]

Hi, Thanks its working.

like the same one more column will create in place of KD will display as null.

like ,

Input			Output
Modes	KD		Modes	KDnew1
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

Thanks again.

[PrashantSangle]

to achieve 2nd output you need one more join with @rwunderlich solutions.

Try below

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
Resident Table
Group by Modes;

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

Drop Tables Table;

Regards,

Prashant Sangle

[nvijay5757]

Hi, Thanks for your reply.

in 25.0AB only nKD that will display nKD, but it is showing null.

[PrashantSangle]

so you mean to say when both value is nKD then it should display nKD only? 

can you confirm below logic

if you have multiple occurrence of  Modes and one of the KD value of that mode is KD then only there nKD will replace with null else not.