topic Re: Null values in Dimenssion Group in QlikView

Null values in Dimenssion Group

Wed, 07 Sep 2016 15:55:39 GMT

Hello.

I have a very tricky question. I hope all you people could help me.

I have a DB with the next schema:

I have created a pivot table as follows:

As you can see, in the first row '(-)' it represents as a NULL value.

'category' comes from the MasterProduct.qvd and it is not because the 'codproduct' does not exist. I have checked in a plain table and it exists. The problem is with 'codproduct': in this column i have some codes like this:

1234E0045
2034E0095

And others like this:

23456T47
45T34E23

With the fist group is where I have the problem. QV transforms as numeric exponential 1234E0045 -> 1.23 x10 ^45

I would like to help me with some advise to avoid this exception. I have changed the code with some of this formulas but it still does not work.

text(codproduct) as codproduct
trim(text(codproduct)) as codproduct
text(codproduct)&'PRD' as codproduct

I hope you can help me.

Thank you in advance.

Diego.

Re: Null values in Dimenssion Group

Wed, 07 Sep 2016 16:49:58 GMT

hi! Try this:

'PRD-'&codproduct as codproduct

that should hopefully result in a string!

Re: Null values in Dimenssion Group

sunny_talwar — Wed, 07 Sep 2016 17:09:43 GMT

Have you tried fixing the codproduct before the creation of qvd or after the creation? I think this needs to be done before you create the qvd.

Re: Null values in Dimenssion Group

Wed, 07 Sep 2016 18:02:05 GMT

All data stored into my QVD files comes from our ERP, from few months before all new productos will no have this 'E' inside product codes. But with old codes is where I have the problems and I can't fix them because those codes are in our systems and it impossible to change them.

Re: Null values in Dimenssion Group

sunny_talwar — Wed, 07 Sep 2016 18:06:57 GMT

Don't have to change them in the system, but before you create your qvds, try using the text functions on the source files. I think the issue is that once they are loaded in the qvd without using text, they change into numbers and then changing them back to text might be the issue what you are facing. I am not 100% certain, but I think that fixing before you save to qvds might help.