Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to replace a "blank" field with a value

Hi all,

I have a simple List Box set up as follows:

listbox.png

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.

1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

13 Replies
sunny_talwar

You can do this in the script:

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

uroboros
Creator
Creator

You can apply map on the xtraccion job

Apply Map Functions - Sources

SreeniJD
Specialist
Specialist

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

Not applicable
Author

Thanks Sunny,

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

Not applicable
Author

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"

sunny_talwar

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

Peter_Cammaert
Partner - Champion III
Partner - Champion III

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

rubenmarin

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.

Not applicable
Author

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$]);