Aggregation rules, group by

Question

Hello everyone,

This is my first post, so I hope it finds you all well and thanks in advance for any support or contribution.

I am a very beginner and on my way to creating a more "advanced" report I am facing some difficulties to achieve my goal and therefore here I am.

In my report I need to create a condition which populates a field  when a specific criteria is met. Basically, I have three fields my table Manufacturer, I have three columns: a product number, a manufacturer role and manufacturer country. I created extra columns BR , PI, QC and SP based on

WHEN "Manufacturer"."Role" = 'BR' AND

"Manufacturer"."Country" = 'UK' THEN 1 ELSE 0)

Product NumberRoleCountryBRPIQCSPP4580BRFR0000P4102BRUK1000P5028BRES0000P5028BRHU0000P5028BRMT0000P5028BRPL0000P5028BRUK1000P5028PIES0000P5028PIHU0000P5028PIMT0000P5028PIPL0000P5028PIUK0100P4580QCFR0000P4580QCUK0010P4580QCUS0000P4102QCUK0010P5028QCES0000P5028QCHU0000P5028QCMT0000P5028QCNZ0000P5028QCUK0010P4580SPFR0000P4580SPUK0001P4102SPUK0001P5028SPNZ0000P5028SPPL0000P5028SPUK0001

This is my first step to identify what is UK and non UK category for every 'role type' in column role. One product can have 4 different roles BR, PI, QC and SP and many country based sites and the rule is;

If UK is the only country with role BR then give me UK only

If UK is non of the countries with role BR then give me No Uk

IF UK is one of the countries in addition to any other then give me UK also.

With my basic sql skills, i would formulate the following

CASE

WHEN MIN("Manufacturer"."Role" = 'BR' AND

"Manufacturer"."Country" = 'UK') = 0

AND

WHEN MAX("Manufacturer"."Role" = 'BR' AND

"Manufacturer"."Country" = 'UK') = 0

THEN 'No UK'

WHEN MIN("Manufacturer"."Role" = 'BR' AND

"Manufacturer"."Country" = 'UK') = 1

AND

WHEN MAX("Manufacturer"."Role" = 'BR' AND

"Manufacturer"."Country" = 'UK') = 1

THEN 'UK only'

WHEN MIN("Manufacturer"."Role" = 'BR' AND

"Manufacturer"."Country" = 'UK') = 0

AND

WHEN MAX(

"Manufacturer"."Role" = 'BR' AND

"Manufacturer"."Country" = 'UK') = 1

THEN 'UK also'

ELSE 'check'

END

GROUP BY "Manufacturer"."Product"

My first problem  is that... there will probably be a much easier way to do this and probably I am over complicating my life. Secondly I don't know why BI gives me errors with this and all the combinations I have tried.... I have also tried the advanced SQL group by, but get errors as well. So, I don't know what else to do. I think our table has behind by default the group by 'product number' column.

Thanks all for giving me a hand.

All the best,

siroco1979 · Answer

Hi @Jerry Casey@,

Thanks, It defo helped, that was it

I added it another condition to fulfil my requirements

WHEN COUNT("Manufacturer"."Country" by "Manufacturer"."Role", Product Number)>= 1 AND "Manufacturer"."Country" NOT IN ('UK','uk') THEN 'No UK'

Else Null

And all works now as I wanted. I also understood the way you played with the group by and the syntax.

Thanks again

Jerry Casey · Answer

If you are only interested in results for the UK, you can skip all of the 1/0 columns in your first step.  A single column containing a much simpler CASE statement will produce what you want.

CASE

WHEN COUNT("Manufacturer"."Country" by "Manufacturer"."Role", Product Number)=1 AND "Manufacturer"."Country"='UK' THEN 'UK Only'

WHEN COUNT("Manufacturer"."Country" by "Manufacturer"."Role", Product Number)>1 AND "Manufacturer"."Country"='UK' THEN 'UK Also'

ELSE 'No UK'

END

About the error you're getting:  The WHEN clauses in your case statement should not be connected by "AND".  Each WHEN is an independent set of conditions, although you can combine conditions within a WHEN clause.

Hope this helps,
Jerry