Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
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
marcus_sommer

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

View solution in original post

4 Replies
swuehl
MVP
MVP

Maybe try an expression like:

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

marcus_sommer

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
Author

Thanks, but Marcus answer worked really good!

Regards

Not applicable
Author

Perfect! Thanks