Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
Do you mean example on the isNull() , search the help on the is null. it is a common function.
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
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)
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
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.
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.
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