BusinessObjects Board

If then else nested statement with AND condtion

building reports webintelligence
, ,
#1

I had got an urgent requirement where we have an existing code and i now want to add nested IF condition with AND

but unfortunately it is creating two rows 1 with blank and other with my value

Existing code

=If([Query 2].[Component]=“Neste Renewable Propane” ;“Neste Biopropane”;

If([MIC ID]=“BioJET”;“HEFA”

;Replace([MIC ID];"#";"")))

Working but Two Rows are coming

=If([Query 5].[Component]=“Neste Renewable Propane” ;“Neste Biopropane”;

If([Query 5].[MIC ID]=“BioJET”;“HEFA”;

If ( ([Query 5].[Bio Criteria Header].[Bio Criteria Header - Key]= “140” And ([Query 5].[Bio Criteria Value] = “Pyrolysis Oil”));(“Pyrolysis Oil” );

If ( ([Query 5].[Bio Criteria Header].[Bio Criteria Header - Key]= “140” And ([Query 5].[Bio Criteria Value] = “Recycled Carbon Fuel”));(“Recycled Carbon Fuel” );

If ( ([Query 5].[Bio Criteria Header].[Bio Criteria Header - Key]= “140” And ([Query 5].[Bio Criteria Value] = “Naphtha”));(“Naphtha” );

If ( ([Query 5].[Bio Criteria Header].[Bio Criteria Header - Key]= “140” And ([Query 5].[Bio Criteria Value] = “Diesel”));(“Diesel” );

If ( ([Query 5].[Bio Criteria Header].[Bio Criteria Header - Key]= “140” And ([Query 5].[Bio Criteria Value] = “Gasoline”));(“Gasoline” );

If ( ([Query 5].[Bio Criteria Header].[Bio Criteria Header - Key]= “140” And ([Query 5].[Bio Criteria Value] = “Naphtha (hydrothermal upgrading)”));(“Naphtha (hydrothermal upgrading)” );

If ( ([Query 5].[Bio Criteria Header].[Bio Criteria Header - Key]= “140” And ([Query 5].[Bio Criteria Value] = “Co-pro naphtha”));(“Co-pro naphtha” );

If ( ([Query 5].[Bio Criteria Header].[Bio Criteria Header - Key]= “140” And ([Query 5].[Bio Criteria Value] = “Co-pro marine fuel”));(“Co-pro marine fuel” );

If ( ([Query 5].[Bio Criteria Header].[Bio Criteria Header - Key]= “140” And ([Query 5].[Bio Criteria Value] = “Co-pro petrol”));(“Co-pro petrol” );

If ( ([Query 5].[Bio Criteria Header].[Bio Criteria Header - Key]= “140” And ([Query 5].[Bio Criteria Value] = “Co-pro LPG”));(“Co-pro LPG” );

If ( ([Query 5].[Bio Criteria Header].[Bio Criteria Header - Key]= “140” And ([Query 5].[Bio Criteria Value] = “Co-pro diesel”));(“Co-pro diesel” );

Replace([Query 5].[MIC ID];"#";""))))))))))))))

Can anyone please help who i can write this code in better way so only 1 row will generate

========================================
I want to add this attach condition to my old code

If Then Else
If Then Else3472×1745 125 KB

#2

Can you wrap your code in a MAX()? Should remove the null value

#3

YES MAX is working

but i still not understand why this else part is also coming as in output.
Replace ([Query 5].[MIC ID];"#";" ")

whereas in existing code no Blank/Else value is coming.

=If([Query 2].[Component]=“Neste Renewable Propane” ;“Neste Biopropane”;

If([MIC ID]=“BioJET”;“HEFA”

;Replace([MIC ID];"#";"")))

Can you confirm the code for nested if then else which i had written in my question is all correct?

and one more

what if two condition satisfy -
in that case if we use max it will return only one row and again that will go wrong.

#4

maybe the problem can het solved by creating a “Group” on the base of the relevant variable

general remark about adding conditions in formulas or to variables: the “where” operator und the “if/then” function behave differently exactly in the question of generating empty rows
so using the where operator could be made possible by concatenating the results of the various cases as long as only leads to results at one time