Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

bonkornwit
Contributor

returning null values

Hey I have a problem with returning null values,

what I'm trying to achieve is to return the hire date (obtain a list of items on hire),

where hire date is <=today, and off hire date >today. however some items may still be on hire and not have a off hire date (Nulls)

Question - how may i bring the nulls into the expression?

this current expression only return 'Hire date' if 'Off hire Date' exists.

MaxString({<Status={'ON HIRE'},Hire_FiscalMonth=,Hire_FiscalYear=,OffHireDate={'>$(vHireYTDEnd)'},HireDate={'<=$(vHireYTDEnd)'}>}HireDate)

Thanks

Bon.

1 Solution

Accepted Solutions
bonkornwit
Contributor

Re: returning null values

I think i may have what i need, at least it looks correct for the time being, may need further checking.

what i did was i added a Null flag in the load script, as per Value NULL in set analysis?

below was added to my load script where offhiredate field is located:

If(Len(OFFHIREDATE) = 0, 1, 0) as OffHireNullFlag

didn't think it would actually work but it did.

in combined with some of the codes provided by Ramon

the following expression was used.

MaxString({<Hire_FiscalMonth=,Hire_FiscalYear=,OffHireDate={'>=$(vHireCurrentMonthStart)'},HireDate={'<=$(vHireCurrentMonthEnd)'}>

+

<Hire_FiscalMonth=,Hire_FiscalYear=,OffHireNullFlag={1},HireDate={'<=$(vHireCurrentMonthEnd)'}>

}HireDate)

what changes made to the code was:

1) removed the statusCode

2) OffHireDate now has a new variable, which states the first date of month instead "=min(hireDate)"

3) HireDate still uses end of month (new variable name)

4) Null flag added to expression in 2nd part instead of OffHireDate

hope this helps anyone trying to achieve similar things.

Thanks all for help

17 Replies
Employee
Employee

Re: returning null values

Set expressions are all about selections (or searches). Can you manually apply a selection that includes the data you are looking for? If the answer is YES, then the set expression usually is fairly easy to compose.

For more advanced set analysis you might need to look at using set operators to find the data area where your results should come from. P() and E() functions can also be handy to target possible and excluded results in advance set analysis.

The Help or reference manual cover the possibilities in the Set Analysis chapter.

To get an accurate answer to your specific questions I think you need to provide a sample QVW, so that your data can be evaluate and put in context of the expression.

Not applicable

Re: returning null values

Many ways you can do this. Using your expression, try this option

MaxString({<Status={'ON HIRE'},Hire_FiscalMonth=,Hire_FiscalYear=,OffHireDate={'>$(vHireYTDEnd)'},HireDate={'<=$(vHireYTDEnd)'}, isnull(OffHireDate)=-1>}HireDate)


isnull() returns -1 as a true value

Employee
Employee

Re: returning null values

I have never seen a construction like that in set expression. Do you have an example where you can show that this principle works?

ramoncova06
Valued Contributor III

Re: returning null values

‌check this post by HIC on how to work with null values

NULL handling in QlikView

bonkornwit
Contributor

Re: returning null values

Hey Toni.

thank you for your reply, how may I upload my QVW? i've reduced the data and scrambled some parts of it, but cannot seem to find the option to upload

Thanks.

Bon

bonkornwit
Contributor

Re: returning null values

Hey Imad.

thank you for your reply, I have tried using your expression, but doesnt seem to work, instead it returned Null for all my HireDate

bonkornwit
Contributor

Re: returning null values

Thank you Ramon,

will go through the reading today

Employee
Employee

Re: returning null values

Yes, the part highlight in bold in Imad's example is not a valid syntax.

Employee
Employee

Re: returning null values

Attach file in existing entry

  1. Select Edit in the Action drop down.
    2015-05-15 13_11_54-returning null values _ Qlik Community.png
  2. Click Attach in the lower right corner of the entry editor.
    2015-05-15 13_12_13-Edit reply to Re_ returning null values _ Qlik Community.png

New reply to a thread

  1. Switch the editor to advanced mode
    2015-05-15 13_14_43-Blank or NULL in Set Analysis _ Qlik Community.png
  2. Click Attach in the lower right corner of the entry editor.
    2015-05-15 13_12_13-Edit reply to Re_ returning null values _ Qlik Community.png
Community Browser