Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

null values go missing on transpose

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.

Labels (2)
3 Replies
Highlighted
Partner
Partner

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. 

Highlighted
Contributor
Contributor

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'.

Highlighted
Digital Support
Digital Support

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.