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.
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
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.
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
I have never seen a construction like that in set expression. Do you have an example where you can show that this principle works?
check this post by HIC on how to work with 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
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
Thank you Ramon,
will go through the reading today
Yes, the part highlight in bold in Imad's example is not a valid syntax.
Attach file in existing entry
New reply to a thread