empty cells not recognised as null

[#1]

Hi there!

I'm a newbie in qlikview and have been encountering issues on loading excel datasets extracted from a software (using SQL). As I'm checking completeness of data, null values (ie., empty cell with absolutely no characters) are what I'm really after. However, empty cells are not recognised as null by qlikview. I'm trying to avoid cleaning up the dataset first before loading to qlikview due to huge amount of data.

Thanks for your valuable input.

Regards

nyarky

[#2]

Hi all

Thanks for your suggestions.

I have 78 data fields (ie., 78 columns) and I would like to apply the same script to all columns. I have been using 'Group ID' in all my scripts and still this one doens't work:

LOAD if(len([Group ID]) > 0, [Group ID], NULL()) as [Group ID],

Thanks again!

[PrashantSangle]

Hi,

Try below,

load if(len(trim([Group ID]))>0,[Group ID]) as [Group ID]

Regards

[avinashelite]

if null is not recognized as null then it mean it contains a space or tab space , it won't be visible and it will not be treated as null. So trim your values and then check of the length  of the field that will work.

if(len(trim(yourfieldname))>0,yourfiledname) as yourfiledname

[#5]

Thanks guys for helping me solve my script!

Unfortunately, it's still not working ... i guess [Group ID] shouldn't be used in the first place. As i have 78 data fields, i created [Group ID], which accounts for the people in my dataset, eg., in my completeness formula: =Count([Group ID]). I guess [Group ID] is not the proper representation of the data fields.

thanks again!

[PrashantSangle]

Hi,

Can you provide your sample app with expected output??

or

Explain little bit more.

Regards,

[#7]

Hi max dreamer                                                        

herewith is a sample constructed dataset extracted from an SQL database, imagine there are null values ...

Group ID	Health System   Number	Name	Surname	Date of Birth	Date of   Diagnosis	Usual   Residential Address	Diagnosis
1	ABC1200
2	DEF1300
3	GHI1400
4	JKL1500

@Avinash R, i'm aware of the spaces or tab spaces that might be existing in the dataset after the extraction and i'd like to get rid of those spaces using scripts to recognise empty spaces.

This is a sample script:

LOAD
filename () as File,
[Group ID],
[Health System Number],
[Name],
[Surname],
[Date of Birth],
[Date of Diagnosis],
[Usual Residential Address], 

[antoniotiman]

Perhaps are You loading data from MySql DB?

[qlikviewwizard]

Nice. Thanks

[#10]

works!!! wonderful