17 Replies Latest reply: May 17, 2015 8:53 PM by bon kornwit RSS

    returning null values

    bon kornwit

      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
          Toni Kautto

          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
              bon kornwit

              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
                  Toni Kautto

                  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
                    • Re: returning null values
                      bon kornwit

                      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
                  Imad Zidan

                  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
                    Ramon Covarrubias

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

                    NULL handling in QlikView

                    • Re: returning null values
                      Ramon Covarrubias

                      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
                        bon kornwit

                        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