topic Re: returning null values in QlikView

returning null values

Anonymous — Fri, 15 May 2015 00:08:53 GMT

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.

Re: returning null values

ToniKautto — Fri, 15 May 2015 00:23:20 GMT

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.

Re: returning null values

Fri, 15 May 2015 01:35:22 GMT

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

Re: returning null values

ToniKautto — Fri, 15 May 2015 01:47:30 GMT

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

Re: returning null values

ramoncova06 — Fri, 15 May 2015 01:48:51 GMT

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

NULL handling in QlikView

Re: returning null values

Anonymous — Fri, 15 May 2015 02:38:56 GMT

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

Re: returning null values

Anonymous — Fri, 15 May 2015 02:40:48 GMT

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

Re: returning null values

Anonymous — Fri, 15 May 2015 02:41:19 GMT

Thank you Ramon,

will go through the reading today

Re: returning null values

ToniKautto — Fri, 15 May 2015 03:11:31 GMT

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

Re: returning null values

ToniKautto — Fri, 15 May 2015 03:17:17 GMT

Attach file in existing entry

Select Edit in the Action drop down.
Click Attach in the lower right corner of the entry editor.

New reply to a thread

Switch the editor to advanced mode
Click Attach in the lower right corner of the entry editor.

Re: returning null values

Anonymous — Fri, 15 May 2015 03:25:31 GMT

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.

Re: returning null values

Fri, 15 May 2015 10:58:39 GMT

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

Re: returning null values

sunny_talwar — Fri, 15 May 2015 11:32:33 GMT

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

Re: returning null values

ramoncova06 — Fri, 15 May 2015 16:18:31 GMT

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)

Re: returning null values

ToniKautto — Fri, 15 May 2015 22:02:55 GMT

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

Re: returning null values

Fri, 15 May 2015 22:37:08 GMT

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.

Re: returning null values

Anonymous — Sun, 17 May 2015 22:34:12 GMT

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.

Re: returning null values

Anonymous — Mon, 18 May 2015 00:49:31 GMT

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