topic Re: Zero for null fields in QlikView

Zero for null fields

carolinne88 — Wed, 25 Nov 2020 16:16:04 GMT

Hello guys! I need help.

I have a data worksheet that does not notify zero for null values.

When I put in the expression to bring in the value of the last month, it returns the value of the preceding month.

I need zero to appear.

Can anyone help?

Re: Zero for null fields

swuehl — Tue, 03 Oct 2017 19:28:52 GMT

How does you expression look like, how does your data (model) look like, what's the context of your expression?

Re: Zero for null fields

carolinne88 — Tue, 03 Oct 2017 19:32:30 GMT

Hi, Stefan.

My expression is: =Count( {< $(vMesRef) >} [natureza jurídica])

vMesRef --> PerID = {$(=Max(PerID))} (PerID is a autonumber).

Re: Zero for null fields

swuehl — Tue, 03 Oct 2017 19:37:12 GMT

=Max(PerID)

in a text box does not bring back the requested month ID?

Or how are the PerID and [natureza jurídica] fields related in your data model, where do you see the NULL?

Could you post some sample records?

Re: Zero for null fields

carolinne88 — Tue, 03 Oct 2017 19:43:49 GMT

Max (PerID) returns yes the last month. The problem is that in the data table that I do not appear the field information without occurrence of values.

Example:
Sales in the month of October / 17:
Salesman
A 100
C 45
D 60

As seller B did not make sales in the month of October / 17, it does not appear in the table.

Re: Zero for null fields

swuehl — Tue, 03 Oct 2017 19:50:46 GMT

So what's the contex of your expression? A table chart with dimension Seller? And any active selections?

Are you talking about seeing the september sales for seller B in your sample instead of october sales, as seen for seller A.C.D?

Re: Zero for null fields

swuehl — Tue, 03 Oct 2017 19:51:36 GMT

If possible, please create and upload a small sample application, I think this will be more efficient to clarify your issue.

Re: Zero for null fields

andrei_delta — Wed, 04 Oct 2017 08:08:08 GMT

Hello,

From what i understand you have null values in the Salesman field and you want the null values to be changed to 0.

you can do that in script with alt(Salesman,0) as Salesman

Hope it helps,

Andrei

Re: Zero for null fields

techvarun — Wed, 04 Oct 2017 08:29:56 GMT

If(IsNull(YourField),0,YourField) as YourField

try the above script while loading the data

Re: Zero for null fields

avinashelite — Wed, 04 Oct 2017 08:46:54 GMT

As per my understanding your facing missing data problem , en order for the sales man B to appear for Oct then you need to have the value first ...follow this thread you will get the answer

Fill in missing dates till today

Generating Missing Data In QlikView

Re: Zero for null fields

HirisH_V7 — Wed, 04 Oct 2017 09:26:08 GMT

Better Specify the Field that contains nulls or empty as you said, As '0' and load it again.

Use Script as Varun suggested for that field in Script Side.

Re: Zero for null fields

carolinne88 — Wed, 04 Oct 2017 11:15:44 GMT

Thanks for the suggestions and I'll check back soon. I'll give you a return. Thank you!

Re: Zero for null fields

Anonymous — Wed, 04 Oct 2017 11:30:13 GMT

try this:

Alt(PerID,0)

Re: Zero for null fields

carolinne88 — Wed, 04 Oct 2017 15:59:00 GMT

Guys, I still have the same problem.

When I put the expression to return the value of the last PerID, as there is no data from the "B seller", it returns the data from the last month that there was sales of B. It does not return zero as I would like.

Re: Zero for null fields

techvarun — Thu, 05 Oct 2017 04:42:48 GMT

A sample qvw or data would help the community to help you better

Re: Zero for null fields

carolinne88 — Thu, 05 Oct 2017 17:27:03 GMT

Hi guys!

Thanks to Varun's advice, I was able to solve my problem. I found this link here in other forum discussions.

Link: https://community.qlik.com/blogs/qlikviewdesignblog/2013/02/05/populating-a-sparsely-populated-field

Thank you all!

Re: Zero for null fields

carolinne88 — Thu, 05 Oct 2017 17:28:41 GMT

by Avinash's tips *

sorry.