Cleansing the data

[Anonymous]

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.

[antoniotiman]

Hi Kushal,

try

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

Regards,

Antonio

[sunny_talwar]

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

Data Cleansing

[Chanty4u · Accepted solution]

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;

[Anonymous]

Thank you all for your valuable timely inputs.

[saimahasan]

Use PurgeChar(column1,'0123456789')

[engishfaque]

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