topic Re: Replace values in App Development

Replace values

nvijay5757 — Mon, 15 Aug 2022 12:12:48 GMT

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.

Re: Replace values

GregAtStretch — Mon, 15 Aug 2022 13:00:36 GMT

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

Re: Replace values

JordyWegman — Mon, 15 Aug 2022 13:10:07 GMT

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

Re: Replace values

rwunderlich — Mon, 15 Aug 2022 13:27:51 GMT

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

Re: Replace values

nvijay5757 — Mon, 15 Aug 2022 15:58:26 GMT

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

Re: Replace values

rwunderlich — Mon, 15 Aug 2022 23:32:52 GMT

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

Re: Replace values

nvijay5757 — Tue, 16 Aug 2022 05:48:10 GMT

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.

Re: Replace values

PrashantSangle — Tue, 16 Aug 2022 11:21:18 GMT

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

Re: Replace values

nvijay5757 — Tue, 16 Aug 2022 11:55:11 GMT

Hi, Thanks for your reply.

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

Re: Replace values

PrashantSangle — Tue, 16 Aug 2022 12:10:36 GMT

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.

Re: Replace values

PrashantSangle — Tue, 16 Aug 2022 12:23:10 GMT

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

Re: Replace values

nvijay5757 — Tue, 16 Aug 2022 12:30:28 GMT

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

Re: Replace values

PrashantSangle — Tue, 16 Aug 2022 12:37:52 GMT

check my above solution