- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
How to replace a "blank" field with a value
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.
- Tags:
- new_to_qlikview
- « Previous Replies
-
- 1
- 2
- Next Replies »
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
You can do this in the script:
If(Len(Trim(FieldName)) > 0, FieldName, 'None Specified') as FieldName
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
You can apply map on the xtraccion job
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks Sunny,
So whereabouts in the script do I put this ? Is it before or after the LOAD table ?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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"
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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$]);
- « Previous Replies
-
- 1
- 2
- Next Replies »