topic Re: Formatting data from excel in QlikView

Formatting data from excel

Wed, 02 Jul 2014 11:58:17 GMT

Hi All,

I am using data from excel in which City column is in below format,

Ajmer

Ajmer.

Bareliu.P.

Bareli U.P

Distt. Kullu H.P.

Distt. Kullu (H.P.)

....

...

....

How can I remove duplicates from the column.?

Re: Formatting data from excel

ashfaq_haseeb — Wed, 02 Jul 2014 12:05:28 GMT

Hi,

Use match function.

Regards

ASHFAQ

Re: Formatting data from excel

hic — Wed, 02 Jul 2014 12:12:13 GMT

I would use ApplyMap to clean data. To do this, you need a table listing the faulty entries and how these should be corrected:

Map:

Mapping Load * Inline

[From, To

Ajmer., Ajmer

Bareli U.P, Bareliu.P.

Distt. Kullu (H.P.), Distt. Kullu H.P. ];

Then you can use this when loading the data:

ApplyMap('Map', City) as City,

HIC

Re: Formatting data from excel

Wed, 02 Jul 2014 12:13:52 GMT

Hi Ashfaq,

There are almost more than 1000 records, so cant use match function.

Re: Formatting data from excel

Wed, 02 Jul 2014 12:21:19 GMT

Hi Henric,

Thanks for ur reply, but I am having more than 1000 records . Do you have any better solution?

Re: Formatting data from excel

Anonymous — Wed, 02 Jul 2014 12:29:52 GMT

Hi Rajeshree

ReplaceMap:

MAPPING LOAD * INLINE [

char replace

)

(

] (delimiter is ' ')

;

String:

Load

MapSubString('ReplaceMap', City) as Replaced_String

;

Load * Inline [

City

Ajmer

Ajmer.

Bareliu.P.

Bareli U.P

Distt. Kullu H.P.

Distt. Kullu (H.P.)

];

Instead of this Inline - "City", load the corresponding column from the source and try.

Vijit

Re: Formatting data from excel

Wed, 02 Jul 2014 12:34:48 GMT

Hi Vijit

Thank you for response . Will try this and get back to you incase of more queries

Best Regards

Your Ex-TeamMate

Re: Formatting data from excel

Wed, 02 Jul 2014 12:43:02 GMT

Hi,

I believe this should work for you! Test once after loading data.

Test:

LOAD * INLINE [

Name

Ajmer

Ajmer.

Bareliu.P.

Bareli U.P

Distt. Kullu H.P.

Distt. Kullu (H.P.)

];

New:

NoConcatenate

Load Name,Peek(Name) as NewName, if(WildMatch(Purgechar(Name,'(.)-_ '),'*'&Purgechar(Peek(Name),'(.)-_ ')&'*')>0,1,0) as Filter

Resident Test

order by Name asc

;

Final:

NoConcatenate

Load NewName

Resident New

Where Filter=1;

Drop Table New;

Drop Table Test;

Regards,

Prabhu