topic Re: How to return null() when using MAX and group BY() in QlikView

How to return null() when using MAX and group BY()

Tue, 19 May 2015 14:23:30 GMT

Hi,

I am calculating if a product was delivered on time, sometimes there can be many deliveries of the same product, i need the latest deliver to be the time used against the expected delivery, but sometimes one of the deliveries has no date insterted yet, and it will use the max date instead of the null()

Example:

Position expected date Delivery date

30 04/04/2015 03/04/2015

30 04/04/2015 null()

Result after: expected date, max(Delivery date) , Group by position;

Position expected date Delivery date

30 04/04/2015 03/04/2015

What i need:

Position expected date Delivery date

30 04/04/2015 Null()

The use of MAX is necessary since sometimes instead of null() i could have 04/04/2015, so it will display the latest delivery.

How do i get the max when is necessary, and the null() when one input is set to null()?

Regards

Re: How to return null() when using MAX and group BY()

swuehl — Tue, 19 May 2015 14:37:23 GMT

Maybe try an expression like:

= If( NullCount([Delivery date]), NULL(), Max([Delivery date]) )

Re: How to return null() when using MAX and group BY()

marcus_sommer — Tue, 19 May 2015 14:41:30 GMT

There are several ways possible. One is to replace the NULL before you aggregated it (directly within a load per if-loop, or per NullAsValue or per Null-Mapping). Another could be to use peek/previous in a sorted load to flag the latest delivery date. Further a pick on concat() on these field is also a possibility. At first I would try something like this:

....

max(if([Delivery date] >= 1, [Delivery date], 1000000)

....

- Marcus

Re: How to return null() when using MAX and group BY()

Tue, 19 May 2015 19:44:18 GMT

Thanks, but Marcus answer worked really good!

Regards

Re: How to return null() when using MAX and group BY()

Tue, 19 May 2015 19:44:30 GMT

Perfect! Thanks