Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
Year | A | B | C |
---|---|---|---|
2009 | 10 | 20 | 13 |
2010 | 43 | 24 | 52 |
2011 | 23 | 52 | 34 |
but sometimes I'd get:
Year | A | B | C |
---|---|---|---|
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!
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
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
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