Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

Force load of blank cells from Excel

Hi!

I'm having a problem with loading a block of data from Excel into QV.

I'm loading data from multiple files, and most of the time i'd have something like:

YearAB
C
2009102013
2010432452
20112352

34

but sometimes I'd get:

YearA
BC
2009


2010


2011


...where the users didn't fill in the data (maybe they didn't have any).

In this case I still need to read it in.

I have a table, where one of the fields is assigned a static value:

...

    'Label1' as [Label],

    @2 as ,

    @3 as ,

...

and later I join it with another table, relying on the [Label] for the correct join.

The problem appears, when I have blank cells, the [Label] never gets assigned the value "Label1" for some reason.

I don't care if the cells are blank, NULL is fine with me, but QV doesn't even store a null character as the value for these cells.

I'm lost, any help is appreciated.

Thanks!

1 Solution

Accepted Solutions
Not applicable

Force load of blank cells from Excel

You could try a formula in the script  for each field like:

...

    'Label1' as [Label],

if(isnull(@2) or @2='','Null',@2) as ,

if(isnull(@3) or @3='','Null',@3) as ,

...

This might populate the blank cells with at least the word Null

2 Replies
Not applicable

Force load of blank cells from Excel

You could try a formula in the script  for each field like:

...

    'Label1' as [Label],

if(isnull(@2) or @2='','Null',@2) as ,

if(isnull(@3) or @3='','Null',@3) as ,

...

This might populate the blank cells with at least the word Null

Not applicable

Force load of blank cells from Excel

Perfect. Thank you marcsliving!

I did try the isnull(), but not the @2=''. Apparently the cells were read in as blank strings and not NULL.

Thanks

Community Browser