topic Re: How do I change incoming text data in QlikView

How do I change incoming text data

Wed, 22 May 2013 14:38:47 GMT

Hi,

Can anyone advise how i can change data being imported from an excel spreadsheet?

I'm reporting fuel transaction data, our supplier has changed the format of the report from showing 'DIESEL' to now showing 'Diesel' & 'Premium Diesel'.

Is there an easy way to make all the new data appear as 'DIESEL'.

I have the same problem with other purchased fuels.

Many Thanks

Re: How do I change incoming text data

rustyfishbones — Wed, 22 May 2013 14:47:22 GMT

Use REPLACE and UPPERFunction

UPPER(REPLACE(MyField,'Premium Diesel','Diesel')) as MyField

Re: How do I change incoming text data

flipside — Wed, 22 May 2013 14:57:32 GMT

Try using a mapping table (essentially works like an Excel lookup) as it's easier to maintain. Consider this example ...

FuelDesc:

Mapping Load * inline [

Lookup, Return

Diesel, DIESEL

Deisel, DIESEL

Gas, PETROL];

Data:

Load *, ApplyMap('FuelDesc',Fuel,'*' & Fuel) as MappedFuel Inline [

Fuel

Diesel

Deisel

Petrol

Gasoline

Gas];

In this you will see I add an asterix to highlight those values which need adding to the mapping table.

flipside

Re: How do I change incoming text data

Fri, 24 May 2013 16:17:45 GMT

Perfect - thankyou.