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: 
haymarketpaul
Creator III
Creator III

Changing blank fields(cells) from an xls to <Not Answered>

Hello

I have an xls file with some blank cells such as this...

MissingAnswers.jpg

When i LOAD the data into QlikView i want to replace the blank cells with the text <Not Answered> which i have attempted to do like this...

     If(Telephone='','<Not Answered>',Telephone) as Telephone,

     If([Email Address]='','<Not Answered>',[Email Address]) as Email

Sadly this is not working though and it just displays the '-' character like so...

MissingAnswers2.jpg

Any ideas why this is not working or is there a better way to do this?

regards

Paul

1 Solution

Accepted Solutions
prieper
Master II
Master II

Give it a try with

IF(LEN(TRIM(Telephone)) = 0, 'not answered', Telephone)     AS Telephone

HTH
Peter

View solution in original post

3 Replies
prieper
Master II
Master II

Give it a try with

IF(LEN(TRIM(Telephone)) = 0, 'not answered', Telephone)     AS Telephone

HTH
Peter

erichshiino
Partner - Master
Partner - Master

Instead of Telephone='' try

len(trim(Telephone))=0

(the trim can help you if there is a space ' ' there)

The NULL was not interpreted as a blank ('')

hope it helps,

Erich

haymarketpaul
Creator III
Creator III
Author

Brilliant, worked perfectly - Thanks Guys