Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
Anonymous
Not applicable
Author

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

View solution in original post

17 Replies
ToniKautto
Employee
Employee

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
Author

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

ToniKautto
Employee
Employee

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
Specialist III
Specialist III

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

NULL handling in QlikView

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

Thank you Ramon,

will go through the reading today

ToniKautto
Employee
Employee

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

ToniKautto
Employee
Employee

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