topic Re: null values go missing on transpose in QlikView

null values go missing on transpose

Quodly — Sat, 16 Nov 2024 01:26:55 GMT

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.

Re: null values go missing on transpose

Stoyan_Terziev — Wed, 22 Jan 2020 09:27:55 GMT

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.

Re: null values go missing on transpose

Quodly — Thu, 23 Jan 2020 08:41:44 GMT

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

Re: null values go missing on transpose

Brett_Bleess — Wed, 19 Feb 2020 17:04:17 GMT

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