topic Re: Include Null values in QlikView

Include Null values

yvonne-c — Sat, 16 Nov 2024 05:14:11 GMT

I have the expression below which is working fine - but I also want to include all IDs where Field "MoveOff" is empty - is there a way to add these into my set analysis?

Count({<Dateon={"<31/10/2018"},MoveOff={">01/10/2018"}ID)

Thanks

Re: Include Null values

sunny_talwar — Thu, 15 Nov 2018 14:02:21 GMT

May be this

Count({<Dateon = {"<31/10/2018"}, ID = {"=MoveOff > MakeDate(2018) or Len(Trim(MoveOff)) = 0"}>} ID)

Re: Include Null values

marcus_sommer — Thu, 15 Nov 2018 14:13:20 GMT

Another way:

Count({<Dateon={"<31/10/2018"},MoveOff={(">01/10/2018"|"=len(trim(MoveOff))=0"}>} ID)

- Marcus

Re: Include Null values

sunny_talwar — Thu, 15 Nov 2018 14:18:21 GMT

Marcus, if MoveOff is truly null, then the comparison to Len(Trim(MoveOff)) = 0 won't really work, I think. What do you think?

Re: Include Null values

shwethaa — Thu, 15 Nov 2018 14:23:28 GMT

Try this, it may work!!

Count({$-<MoveOff={"*"}>+<Dateon={"<31/10/2018"},MoveOff={">01/10/2018"}>ID)

Re: Include Null values

marcus_sommer — Thu, 15 Nov 2018 14:37:18 GMT

With empty values it will work but not with NULL because if there is none value respectively NOTHING it couldn't be measured through itself - else an approach like in your example with measuring it with the help of a different field is needed.

- Marcus

Re: Include Null values

Vegar — Thu, 15 Nov 2018 22:26:59 GMT

The "problem" with SET is that you can't select something that does not exists. You can't "select" Null() values with your modifer.

What if you instead exclude MoveOff prior to 01/10/2018

```
Count({<Dateon={"<31/10/2018"},MoveOff-={"<=01/10/2018"}ID)
```

Re: Include Null values

yvonne-c — Fri, 16 Nov 2018 08:51:13 GMT

Thanks for all the replies - I just can't get it to work though

Link to some sample data here I hope CountTest - don't seem to be able to attach excel file?!

Re: Include Null values

Vegar — Fri, 16 Nov 2018 09:28:28 GMT

If Eartag is an unique identifier for your table rows then you could use this expression:

Count({<
	Dateon= {"<$(=date('31/10/2018'))"},
	Eartag = E( {< MoveOff={"<$(=date('01/10/2018'))"}>})
>}ID)

Cheers

Vegar

Re: Include Null values

shwethaa — Fri, 16 Nov 2018 09:57:59 GMT

Hey Try below expression. It is working for given data.
Count=4
=Count({-<MoveOff={"*"}>+<Dateon={"<31/10/2018"},MoveOff={">01/10/2018"}>} ID)

-Shwetha

Re: Include Null values

yvonne-c — Fri, 16 Nov 2018 10:03:49 GMT

Thanks so much - this one is working!