null values go missing on transpose

[Quodly]

null values are not recognised as such when applying

filters(transpose())

on an xlsx load.

can one interpret these blanks as nulls without specifying fields?

please see the attached files.

[Stoyan_Terziev]

Hi Quodly,

First - that's one interesting finding. Seems QV replaces nulls with empty values on the back-end when doing data transposition.

You are probably searching for a function that does something like 'ValueAsNull'. But to my knowledge, there isn't any.

You can automatically get all the columns in a source (if it's Qvd - use load as an XML, if it's excel - ODBC + SQLColumns;) and for each of the number of columns in your source do something like:

If([$(vColumnName)]='', null(),[$(vColumnName)]) as [$(vColumnName)]

That way you can work with variable number of columns. But this could be overdoing it a bit.

You can unify all nulls doing this:

NullAsValue *;

Set NullValue = '';

And at least make sure that all nulls are empty strings now.

I hope that helps!

Kind regards,

S.T. 

[Quodly]

hi stoyan,

thanks for your reply!

my question is somewhat related to this one:

https://community.qlik.com/t5/QlikView-App-Development/NullInterpret-for-Excel-sources/td-p/1514081

i think that

tmp_map:
mapping load
  ''     as a,
  null() as b
autogenerate 1;

map * using tmp_map;

can be considered as something like 'ValueAsNull'.

[Brett_Bleess]

Well, not sure this is really going to help, but I am going to toss it out anyway:

https://community.qlik.com/t5/QlikView-Documents/NULL-handling-in-QlikView/ta-p/1484472

There is a Technical doc attached and also some Design Blog posts as URLs too, hopefully one of them may help you get things sorted.

Regards,
Brett