Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

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

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

1 Solution

Accepted Solutions

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

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

4 Replies
MVP
MVP

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

Maybe try an expression like:

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

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

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

Not applicable

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

Thanks, but Marcus answer worked really good!

Regards

Not applicable

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

Perfect! Thanks

Community Browser