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.

17 Replies
Anonymous
Not applicable
Author

thanks for that, uploaded

hope it all make sense,

if you look at the expression you will see a few there, those are there because one container may have more than one line of actions associated with it, therefore i would like to select the latest record in the field based on hire date.

and the expression posted above would only return those with an off hire date, those which null (still on hire) or not returned.

thanks.

Not applicable
Author

Do you mean example on the isNull() , search the help on the is null. it is a common function.

sunny_talwar

Try this:

=MaxString({<CONTAINERSTATUSCODE={'ON HIRE'},

  Hire_FiscalMonth=,

  Hire_FiscalYear=,

  OffHireDate={'>=$(vHireYTDEnd)'}+{"=Len(Trim(OffHireDate)) > 0"},

  HireDate={'<=$(vHireYTDEnd)'}>}HireDate)


UPDATE: Does not work, trying to get a correct solution



I hope this will help.

Best,

Sunny

ramoncova06
Specialist III
Specialist III

this should do it, first you have one expression that include a items with an offhiredate, and then you run a second set for the ones that have a null value for OffHireDate and then you join them(I used CONTAINERID as an identifier, you can switch this for whatever you want)

MaxString({

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

+

<CONTAINERSTATUSCODE={'ON HIRE'},Hire_FiscalMonth=,Hire_FiscalYear=,OffHireDate={'>$(vHireYTDEnd)'},HireDate={'<=$(vHireYTDEnd)'}, CONTAINERID ={"=nullcount(OffHireDate)>0"}>

}HireDate)

ToniKautto
Employee
Employee

I am very familiar with the IsNull() function. To my knowledge your suggestion is not a valid expression. My question is if you have an example QVW where you can show that this construction works?o

Not applicable
Author

I understand. It was just a thought and an idea that one can build on. Its all about sharing ideas I guess (right or wrong).

I agree with you however, it is not a valid syntax. I checked. Many thanks for this. 

Anonymous
Not applicable
Author

sorry for the delay to reply.

no luck with any of the solutions provided yet. will update if i find a way to fix this.

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