topic Re: How to replace a "blank" field with a value in QlikView

How to replace a "blank" field with a value

Wed, 14 Oct 2015 13:42:11 GMT

Hi all,

I have a simple List Box set up as follows:

Quite simply, I want that "blank" value (it is not NULL - but I don't know what it actually is either) to be "None Specified" - I've messed about with various IF statements but I can't manage it.

Could someone point me in the right direction ?

I would really appreciate it.

Re: How to replace a "blank" field with a value

sunny_talwar — Wed, 14 Oct 2015 15:24:49 GMT

You can do this in the script:

If(Len(Trim(FieldName)) > 0, FieldName, 'None Specified') as FieldName

Re: How to replace a "blank" field with a value

uroboros — Wed, 14 Oct 2015 15:27:34 GMT

You can apply map on the xtraccion job

Apply Map Functions - Sources

Re: How to replace a "blank" field with a value

SreeniJD — Wed, 14 Oct 2015 16:48:27 GMT

Blank values and NULL values are different.. As Sunny suggested you have to first trim your string (which ideally removes any extra spaces before or after the string), once this has been done.. then check the length of the string...obviously after trimming the spaces string of a blank row will have ZERO length, so expression as suggested by @Sunny will definitely works..

Sreeni

Re: How to replace a "blank" field with a value

Wed, 14 Oct 2015 19:42:37 GMT

Thanks Sunny,

So whereabouts in the script do I put this ? Is it before or after the LOAD table ?

Re: How to replace a "blank" field with a value

Wed, 14 Oct 2015 20:22:58 GMT

Trying to get my head around why this wouldn't work by inserting this into the expressions for the list box....

If([Region of Install]='', 'None Specified', [Region of Install]) as [Region of Install]

It tells me there is "garbage after as"

Re: How to replace a "blank" field with a value

sunny_talwar — Thu, 15 Oct 2015 07:09:28 GMT

This can replace the FieldName in the same table it is getting in

Instead of doing

LOAD FieldName

you would do

LOAD If(Len(Trim(FieldName)) > 0, FieldName, 'None Specified') as FieldName

Re: How to replace a "blank" field with a value

Peter_Cammaert — Thu, 15 Oct 2015 07:24:43 GMT

AS is a script keyword that defines a new name for a field value. It's not an expression keyword. Just keep the preceding part:

IF (len(trim([Region of Install])) = 0, 'None Specified', [Region of Install])

You should do this in the script as to not impose additional load on document performance when the script can take care of that.

Best,

Peter

Re: How to replace a "blank" field with a value

rubenmarin — Thu, 15 Oct 2015 07:28:15 GMT

Hi Andy, the 'as' means it was a solution to use in the script, when you're loading data.

To use directly in an expression you should avoid the 'as':

If([Region of Install]='', 'None Specified', [Region of Install])

Usually is better to do the transformations in script, helps on performance and stability of the data around the document.

Re: How to replace a "blank" field with a value

Thu, 15 Oct 2015 09:22:42 GMT

Thanks Peter,

This is my script, where do I put that IF statement in here ?

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='£#,##0.00;-£#,##0.00';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

LOAD Number as SLTN,

[Project Name],

[Project Type],

[Portfolio Priority],

[Operating System(s)],

[Region of Install],

Stage,

[Assigned group],

Assignee,

[Solutions Assignment Date],

[Design Approved],

[Solution Delivery Date],

[Actual Application Go Live Date],

[Requested Delivery Date],

Name,

Name1,

[Business Department],

[Development Head],

Name2 as [Primary Application],

[External ID],

[Annualized PL (L2)],

[Annualized Saving L2],

[Budget Year PL (L2)],

[Budget Year Saving L2],

[Budget Year Capital (L2)]

FROM

I:\qv\sndata_testqv.xls

(biff, embedded labels, table is [Page 1$]);

LOAD Number1 as SLTN,

[Region Code],

[Type Of Request],

[System Type],

[Operating System Type],

[Operating System Version]

FROM

I:\qv\bomdata_testqv.xls

(biff, embedded labels, table is [Page 1$]);

Re: How to replace a "blank" field with a value

raajaswin — Thu, 15 Oct 2015 09:45:31 GMT

=if(isnull([Region of install]),'REPLACEMENT STRING',[Region of install])

Type this in list box expression..

Re: How to replace a "blank" field with a value

Thu, 15 Oct 2015 10:36:32 GMT

** BOOM **

That's done it, thanks Sunny.

Thanks all for your help, really appreciated.

Andy

Re: How to replace a "blank" field with a value

rubenmarin — Fri, 16 Oct 2015 07:37:50 GMT

Hi Andy, in this part of the script:

LOAD Number as SLTN,

[Project Name],

[Project Type],

[Portfolio Priority],

[Operating System(s)],

[Region of Install],

If(Len(Trim([Region of Install]))=0, 'None specified', [Region of Install]) as [Region of Install]
Stage,

[Assigned group],

....

Re: How to replace a "blank" field with a value

Peter_Cammaert — Fri, 16 Oct 2015 08:11:40 GMT

In addition to that, my suggested expression was meant to fix your listbox. Your OP stated that you had problems displaying a string instead of a blank value in a listbox.