Groupping Data

[#1]

Hello,

I have a table as follows which includes ledger entries (a huge table located in SQL Database). I need to summarize this data into groups related to departments and Account No.

Base Table:

Account No	Department	Value
201199	100.02.001	10
201199	100.02.001	30
201199	100.01.001	20
201199	200.02.001	25
201199	200.01.001	200
201199	300.04.000	100
201200	100.02.001	150
201200	100.02.001	250
201200	200.01.002	90
201200	200.02.005	23
201200	300.04.001	10
201200	300.03.001	5

What I want:

Account No	Group1	Group2	Group3
201199	60	225	100
201200	400	113	15

Group1 includes all values of departments which begins with 100.*

Group2 includes all values of departments which begins with 200.*  etc.

I have also combinations in my data for example Group5 includes all deparments begining with 700.* and 195.001.001

Can someone give me a hand about this. I made a pivot table using 3 dimensions (Account No,Deparment, Value) and 1 expression (Sum of Value) but my question is groupping the departments as table above.

Thanks in Advance , Anil

[#2]

Hello,

If you want to handle this in the script you will have to use the function group by.

E.g.

Load

     Account_No,

     left(Department, 1) as Group,

     sum(Value) as Value

From...

group by Account_No left(Department,1);

br

Martin

[SunilChauhan]

please see the attched file

hope this helps

[its_anandrjs]

Hi,

See the attached I think it is your solution.

Regards

Anand

[its_anandrjs]

Hi.

See the updated attached sample by the help of this code

=IF(Left(Department,3)=100,'Group1',IF(Left(Department,3)=200,'Group2','Group3')).

Regards

Anand

[Jason_Michaelides]

Martin has given you a solution for grouping in the script.  However if you want to retain the transactional detail and group in the chart object then just add

'Group ' & left(Department, 1) as Group

or

'Group ' &left(Department, 3) as Group

or

'Group ' & SubField(Department,'.',1) as Group  - this is the best solution as it allows for growth of the group prefix (ie you don't have o define how many characters to take from the beginning)

...to the load script and use Group as a dimension in the chart.

Jason

[#7]

Thanks a lot, every answer seems to be correct, I did not expected so much quick replies

I have another little question (little for you - big for me)

How can I build groups like:

group5: including departments beginning with 100.* AND 400.001.002

Thanks,

[#8 · Accepted solution]

You should be able to use some of the code provided by one of the guys to create that no problem

For example from Anand's you can incorporate

=IF(Left(Department,3)=100 Or Department=400.001.002 ,'Group5')

thanks

Joe

edit:

sorry it's an OR you want in that statement not an AND my mistake

[SunilChauhan]

you can add any numbers of group

like below

first refer my attached file change the code as below

if(left(Department,3)=100,'Group1',

if(left(Department,3)=200,'Group2',

if(left(Department,3)=300,'Group3'))) as Groups

if u have 4 gruop   100.,200.,300.,400.

then

if(left(Department,3)=100,'Group1',

if(left(Department,3)=200,'Group2',

if(left(Department,3)=300,'Group3',

if(left(Department,3)=400,'Group4'))) as Groups

if u have 5 gruop   100.,200.,300.,400,500

then

if(left(Department,3)=100,'Group1',

if(left(Department,3)=200,'Group2',

if(left(Department,3)=300,'Group3',

if(left(Department,3)=400,'Group4',

if(left(Department,3)=500,'Group5')))) as Groups

[its_anandrjs]

Hi,

Yes correct as Joe says you can add n no of Groups on the syntax

=IF(Left(Department,3)=100,'Group1',

  IF(Left(Department,3)=200,'Group2',

  IF(Left(Department,3)=300,'Group3'

  IF(Left(Department,3)=400,'Group4','Group5'

  ))))

or also you able to change the condition in Left(Department,7)=400.001

it is depent on your condition and usage.

Regards,

Anand