topic Re: Missing value in QlikView

Missing value

detmawin — Sat, 16 Nov 2024 21:11:13 GMT

Hello,

I am trying to create add the following data in the script. I have the following logic:

Load id, MonthYear, max(date) resident table group by ID, MonthYear

My question is: if the month year does not have a field to max, I want to min the first field and past it into the month year field for the missing month.

I'm not sure how to incorporate this logic.

Thanks

Re: Missing value

Vegar — Sun, 24 Mar 2019 04:55:46 GMT

I'm not sure what you are trying to accomplish. You write about "min the first field", do you want MIN(id) for all id/MonthYear that do not have a date?

I'm pretty sure I've misinterpreted you question. Could you please explain what you are trying to accomplish? Please give an example on a normal case and the special case that you are trying to handle.

Re: Missing value

Vegar — Sun, 24 Mar 2019 05:09:15 GMT

Please look at my previous comment about explaing your issue a bit more. Below are some general tips related to your question, hopefully they can be of help.

If you want to populate a field with an alternative value if the default value is null then you could use the ALT() function.
The expression
ALT(min(date), max(date),id)
Will return the id if min(date) and max(date) returns null.

You can create a date by knowing only year and month using makedate(). E.g. if YearMonth is 201901 you could get the last day of month by the expression below.

Dayname(MonthEnd(Makedate(left(YearMonth,4), right(YearMonth,2))))