Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
please see the attched file
hope this helps
Hi,
See the attached I think it is your solution.
Regards
Anand
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
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
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,
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
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
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