Conditional grouping from multiple fields

[2012385096]

Hi,

How would i do conditional grouping from the multiple fields? For example,
Id Name Err1 Err2 Err3 Err4 Err5
1 ABC 1 0 0 1 0
2 XYZ 0 1 0 1 1
3 DEF 1 1 1 1 1

I need to do grouping from the Err fields where value = 1. My group by should look like this
Group : Err1 and Err4
1,ABC
Err2, Err4 and Err4
2,XYZ
Err1, Err2, Err3, Err4 and Err5
3,DEF

Thanks in advance for your help.

 

[IanWaterman]

I think you will have to write a formula which covers all the permutations

@Group
If Err1 = 1 and Err2 = 0 and err3 = 0 and err4 = 0 and err5 = 0 then 'err1' else
.
.
If Err1 = 1 and Err2 = 1 and err3 = 0 and err4 = 0 and err5 = 0 then 'err1 & Err2' else

As its 5 fields that 5! permutations 5x4x3x2x1 = 120

Have fun!!

Ian

PS I hope some one else has a better idea

 

[2012385096]

Hi,
Thanks for your response.

I just put the 5 fields but actually there are 12 fields. So it will be very difficult.

Instead of that even this works for me. How would i get this?

Group: (where value = 1)
Err1
ABC
DEF
Err2
XYZ
DEF
Err3
DEF
Err4
ABC
XYZ
DEF
Err5
XYZ
DEF

 

[IanWaterman]

You can not count data twice, the only way to do it will be to denormalise your data by using a Command and unioning the sets of data together.

Select 'Err1' as Type, t.*
from Table T
where Err1 = 1
Union all
Select 'Err2' as Type, t.*
from Table T
where Err2 = 1
Union all
Select 'Err3' as Type, t.*
from Table T
where Err3 = 1
Union all

etc

You can then group on the field Type from the command

Ian

 

[lbass]

I think you can just create a formula that concatenates the err fields and then group on that, and add the ID and Name field to the detail section. This assumes the "err" fields are detail level fields, not summaries. Assuming these are numbers, not strings, the formula would look like this:

totext({table.err1},0)+totext({table.err2},0)+totext({table.err3},0)+//continue to err12)
totext({table.err12},0)

-LB