topic Re: Cleansing the data in QlikView

Cleansing the data

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

Qlikview gurus

I have the source table as mentioned below

Source table

ID,column1

1 INS

2 INS1

3 INS2

4 INS_id

5 INSUR1

6 INSUR2

Where data has to be clean as follows(removing the extra 1,2 etc things from the keyword like INS and INSUR)

Source table

ID,column1

1 INS

2 INS

3 INS

4 INS

5 INSUR

6 INSUR

Kindly help as I am stuck in between.

Re: Cleansing the data

antoniotiman — Thu, 28 Jul 2016 08:46:30 GMT

Hi Kushal,

try

PurgeChar(SubField(column1,'_',1),'0123456789')

Regards,

Antonio

Re: Cleansing the data

sunny_talwar — Thu, 28 Jul 2016 09:05:47 GMT

I would use a mapping table approach if the number of variations are not too many

Data Cleansing

Re: Cleansing the data

Chanty4u — Thu, 28 Jul 2016 09:21:01 GMT

try this

Data:

LOAD * INLINE [

ID,column1

1 ,INS

2 ,INS1

3 ,INS2

4 ,INS_id

5 ,INSUR1

6 ,INSUR2

];

Result:

LOAD *,

KeepChar(column1,'ABCDEFGHIJKLMNOPQRSTUVWXYZ') AS Result

Resident Data;

Re: Cleansing the data

Anonymous — Thu, 28 Jul 2016 09:54:11 GMT

Thank you all for your valuable timely inputs.

Re: Cleansing the data

saimahasan — Thu, 28 Jul 2016 09:55:32 GMT

Use PurgeChar(column1,'0123456789')

Re: Cleansing the data

engishfaque — Thu, 28 Jul 2016 10:12:40 GMT

Dear Kushal,

Here are the ways to clean your data, for more kindly find attached file.

1. KeepChar Example:

KeepChar(column1,'_ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz')

as Field_KeepChar

KeepChar(column1,'ABCDEFGHIJKLMNOPQRSTUVWXYZ')

as Field_KeepChar

2. PurgeChar Example:

PurgeChar(column1, '0123456789')

as Field_PurgeChar

3. Replace Example:

Replace(Replace(column1, '1', ''), '2', '')

as Field_Replace

Output:

Kind regards,

Ishfaque Ahmed