topic Data Cleaning in QlikView

Data Cleaning

dsharmaqv — Wed, 25 Nov 2020 16:16:04 GMT

ID	Business line
1	Retail Banking,Privet Banking,Whole Sale
2	Retail Banking(RB);Privet Banking(PB),Whole Sale
3	Retail Banking(RB);Privet Banking(PB);Whole Sale

Output

ID	Business Line
1	Retail Banking
1	Privet Banking
1	Whole Sale
2	Retail Banking
2	Privet Banking
2	Whole Sale
3	Retail Banking
3	Privet Banking
3	Whole Sale

Can some one help me achiving the out put

I tried with subfield function but subfield is not able to remove ','

Re: Data Cleaning

MK_QSL — Tue, 17 Jan 2017 09:18:26 GMT

Load

ID,

SubField([Business line],',') as [Business Line]

From TableName;

Re: Data Cleaning

dsharmaqv — Tue, 17 Jan 2017 09:20:46 GMT

can I have the sample app please

Re: Data Cleaning

MK_QSL — Tue, 17 Jan 2017 09:22:03 GMT

//Load this in your QVW and run the script.

Data:

LOAD ID,

SubField(Replace([Business line],';',','),',') as [Business Line]

FROM

[https://community.qlik.com/thread/246434]

(html, codepage is 1252, embedded labels, table is @1);

Re: Data Cleaning

stabben23 — Tue, 17 Jan 2017 09:30:04 GMT

You need to replace first, then use subfield.

Temp:
Load *,
subfield(BussinesLine1,',') as BussinesLine2
;
LOAD *,
Replace(BussinesLine,';',',') as BussinesLine1
;
LOAd * inline [
Id, BussinesLine
1, "Retail Banking,Privet Banking,Whole Sale"
2, "Retail Banking(RB);Privet Banking(PB),Whole Sale"
3, "Retail Banking(RB);Privet Banking(PB);Whole Sale"
];

Re: Data Cleaning

stabben23 — Tue, 17 Jan 2017 09:32:39 GMT