11 Replies Latest reply on May 21, 2015 2:51 PM by 1062047

    How to remove null lines using CASE function

    1062047

      Hi All,

      I am using CASE FUNCTION, to retrieve data from a subject area.

      The command is:

       

      CASE "Candidate Q&A"."Question Code" WHEN 'R3 questionnaire 3' THEN "Candidate Q&A"."Answer (Choice) (BL)" END

       

      Results show the correct data plus a line with null value.

       

      I'd like to show only the value not null.

       

      Is it possible using CASE function or other function? I tryied to use FILTER function, but this function doesn't work how I want.Untitled.png

      Thanks!

      Carlos.

        • 1. Re: How to remove null lines using CASE function
          asim cholas

          hello Carlos,

           

          It is because you are not specified an else condition for you Case statement. You can do this in both ways.

           

          1)If you donot have any null values for your answer columns simply put a filter for your case statement column "is not null"

           

          2)If you have null values in your database and you want to display that but don't want to display nulls due to case statement write a statement like below,

           

          CASE when "Candidate Q&A"."Question Code" = 'R3 questionnaire 3' THEN "Candidate Q&A"."Answer (Choice) (BL)" ELSE 'ASDFGG' END


          Keep some random value in else part and filter your report with Case column not equal to ASDFGG


          You are done


          Thanks

          Aj

          1 person found this helpful
          • 2. Re: How to remove null lines using CASE function
            1062047

            Hi Aj, thank you for your answer!

            I tried to to put an ELSE condition, but yet I don't get the result I need. Because if I create a filte NOT EQUAL 'ASDFGG', other information will be removed. As I have other anwser in the second column, its value would 'ASDFGG':

             

            Untitled.png

            First grid is the actual result, and second grid is the result I would need to retrieve.

            Because there are 1:N answers per candidate, maybe the CASE function will not work as I need.

             

            Let me know if I could explay.

             

            Thank you!

            Carlos.

            • 3. Re: How to remove null lines using CASE function
              asim cholas

              You seems tw have two columns. Try the below.

               

               

              Length(your answer1 column formula)+Length(youranswercolumn2 formula) <> 0

               

               

              Apply a filter like this. convert to sql option in filter will make it easy.

               

              Thanks

              Aj

              • 4. Re: How to remove null lines using CASE function
                Gianni Ceresa

                Hi,

                Maybe I'm just missing something but ....

                Why don't you add as filter in your analysis "Answer1 IS NOT NULL" OR "Answer2 IS NOT NULL" ?

                Just the "IS NOT NULL" (one of the standard filter conditions and you set it for the 2 columns with a OR in a "sub-filter" block (so making sure the 2 with OR will then be matched to your other filters with a AND) ?

                • 5. Re: How to remove null lines using CASE function
                  asim cholas

                  but it can filter out data even any one of the column has values..

                   

                  Thanks

                  Aj

                  • 6. Re: How to remove null lines using CASE function
                    Gianni Ceresa

                    Hi Carlos,

                    Reading it twice makes it as confusing as the first time ...

                     

                    Your first picture is ambiguous: you want to remove Null values but you are doing a pivot on your answers (that's what the CASE WHEN does) and creating tons of NULL cells in your table.

                    So are you trying to remove the line where "Answer 1" and "Answer 2" are NULL or to remove every NULL cell and aggregate your rows by "Candidate Identifier" resulting in (based on your first picture) just 3 rows?

                    47904      Sim   Generalista

                    1008095   Sim   Generalista

                    1008097   Sim   Generalista

                     

                    Because if you look for this "aggregated" version where you do a pivot of your answers it's a completely different problem .... (solved by something else)

                    • 7. Re: How to remove null lines using CASE function
                      1062047

                      Thanks Aj and Gianni,

                       

                      @Gianni,

                      Yes, I am look for aggregated version, I want to remove every NUUL cell and aggregate rows by "Candidate Identifier" resulting in just 3 rows, like you explain in your answer.

                       

                      Thank you so much!

                      Carlos.

                      • 8. Re: How to remove null lines using CASE function
                        Gianni Ceresa

                        Ok, so different problem different solution ...

                         

                        A quick&dirty one (but it works) is to transform your Answer X (X = 1,2,etc.) columns in measures. It's a text, so only few aggregation rules works...

                         

                        If you change your Anserws formulas to be something like:

                        MAX(CASE "Candidate Q&A"."Question Code" WHEN 'R3 questionnaire 3' THEN "Candidate Q&A"."Answer (Choice) (BL)" END)

                         

                        I assume you have only 1 answer for each Candidate Identifier in every one of your Answer columns, so doing a MAX() just keep that value and the NULLS magically disappear. In case you have a case with more than just 1 answer for a candidate in one of your columns it will require something else (more like doing a LISTAGG to really aggregate text as it's what you are trying to do).

                        1 person found this helpful
                        • 9. Re: How to remove null lines using CASE function
                          1062047

                          Great Gianni,

                          It solved my question.

                          Yes, I have only 1 answer for each candidate identifier.

                           

                          Final result:

                          Untitled.png

                           

                          Thank you!

                          Aj, thank you too!

                           

                          Carlos.

                          • 10. Re: How to remove null lines using CASE function
                            Gianni Ceresa

                            Great it worked Carlos.

                            So one last step for you: close the thread so others with similar questions will know they can find something here ... Right not it's still This question is Not Answered.

                            • 11. Re: How to remove null lines using CASE function
                              1062047

                              Thanks Gianni,

                              Let me know if I closed the thread!

                               

                              Regards

                              Carlos.