1 person found this helpful
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
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':
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.
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.
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) ?
but it can filter out data even any one of the column has values..
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)
Thanks Aj and 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!
1 person found this helpful
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).
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.
Let me know if I closed the thread!