1 Reply Latest reply: Jul 8, 2016 9:26 AM by Marcus Sommer RSS

    Set Analysis Null Value

    Chris Ellingworth

      Hi, I've found a lot of discussions about counting null values in set analysis and I wanted to share an observation that I made:


      I couldn't work out why the following expression stopped working for me:



      Max({<[REGION_NAME]={'Goulburn/Murray'},[SITE_NAME]={''}>} [12WK_RATE])

      then I found it was because I had converted the CSV file to an Excel file. I had a number of different expressions and they were still fine after converting to an Excel file except for the expressions that included ={''}. These no longer worked at all. They weren't seeing the empty fields as empty any longer. I tried ={'*'} and this would see the cells with something in them, but -={'*'} would not work. It was still seeing the empty cells as having something in them. I went to the source file and made searches for fields with nothing in them and this showed them as empty and there are no formulas in them either. I reverted back to a CSV file for the source and all the set analysis expressions worked again.





        • Re: Set Analysis Null Value
          Marcus Sommer

          Without a need to adjust or to add data you shouldn't convert a txt/csv-file into an excel-file and even then it might be useful to convert it back into txt/csv-file again. The handling from txt-files to excel-files differs by formatings and NULL's and for this are also various null-variables available to adjust the null-handling. A very good starting point could you find here: NULL handling in QlikView.


          Beside them your above used approach from -={'*'} should work. Maybe these fieldvalues aren't NULL else contain any invisible content. You could check for example with functions like len() or ord() which content is really there.


          - Marcus