topic Re: Replace blanks on load in QlikView

Replace blanks on load

Fri, 02 Mar 2012 08:35:56 GMT

Hi All , I'm a "Newbie" who has only been privvy to an overview course and am now attempting to write some apps. I have an issue in loading a file from an iSeries which contains blanks in one of the fields , which is wreaking havoc with the processing. I've searched high and low for code to replace the blanks - in this case its a currency field which contains blanks and I want to default to GBP , the field is 4 chars long. Any help would be greatly appreciated. Everything I've tried so far either breaks the links or just doesn't replace the blanks. We're on Version 10.

Re: Replace blanks on load

gandalfgray — Fri, 02 Mar 2012 08:45:29 GMT

you can try something like

if(len(trim(currencyField))=0,'GBP',currencyField) As currencyField

in your load statement

trim removes leading spaces

len calculates the length, so if it was only containing blanks there would be nothing left after the trim,

in that case use the hardcoded string 'GBP' otherwise use whatever is in currencyField

hth/GandalfGray

Replace blanks on load

Fri, 02 Mar 2012 09:03:27 GMT

Hi, thanks for the swift reply , did as suggested and works fine - I tried something similar yesterday but must have got syntax wrong !

Many thanks,

Simon

Re: Replace blanks on load

CELAMBARASAN — Fri, 02 Mar 2012 09:08:13 GMT

Hi,

Try with this

Load

if(len(trim(currencyField))=0 or isNull(currencyField),'GBP',currencyField) As currencyField

From

Or use the following one.

GBP:

Mapping LOAD '','GBP' AutoGenerate 1;

MAP CurrencyFieldName using GBP;

This(mapping) should be done before loading the table.

Celambarasan

Replace blanks on load

gandalfgray — Fri, 02 Mar 2012 09:09:25 GMT

Hi Simon

Glad to be able to help

But you should really mark my answer as "correct" answer, not your reply...