topic Groupping Data in QlikView

Groupping Data

Fri, 02 Sep 2011 12:27:17 GMT

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

Groupping Data

Fri, 02 Sep 2011 12:33:15 GMT

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);

Martin

Re: Groupping Data

SunilChauhan — Fri, 02 Sep 2011 12:35:30 GMT

please see the attched file

hope this helps

Re: Groupping Data

its_anandrjs — Fri, 02 Sep 2011 12:37:10 GMT

Hi,

See the attached I think it is your solution.

Regards

Anand

Re: Groupping Data

its_anandrjs — Fri, 02 Sep 2011 12:40:48 GMT

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

Re: Groupping Data

Jason_Michaelides — Fri, 02 Sep 2011 12:41:09 GMT

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

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

'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

Groupping Data

Fri, 02 Sep 2011 13:44:02 GMT

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,

Re: Groupping Data

Fri, 02 Sep 2011 13:50:01 GMT

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

Re: Groupping Data

SunilChauhan — Fri, 02 Sep 2011 13:53:24 GMT

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

Groupping Data

its_anandrjs — Fri, 02 Sep 2011 13:57:14 GMT

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

Re: Groupping Data

rwunderlich — Fri, 02 Sep 2011 14:44:18 GMT

Consider using wildcard mapping as an easy way to organize this. There is an example of mapping with wilcards in the QV Cookbook. I've simplified the wildmap example for the next version, but it's not published yet. I've attached the newer version of the example.

-Rob

http://robwunderlich.com

Groupping Data

its_anandrjs — Sat, 03 Sep 2011 05:56:01 GMT

Hi,

Is this your answer that you want if so let me know about that and tell me about your more requirement if it is not solve.

Regards

Anand

Groupping Data

Sun, 04 Sep 2011 14:28:28 GMT

Hi Anand,

thank you for your reply. Actually no it is not the correct answer. I guess I could not tell you my problem. I want to create a group so it includes all of the records beginning with 100 and one record which is 400.001.002

I guess jsimmons answer is the correct one. I'll tell you if I try it. Thank you again for your interest.

Groupping Data

pablolabbe — Fri, 30 Sep 2011 19:31:43 GMT

Rob,

Your tip was very valuable for me. I was thinking to use pick and wildmatch, but have no idea how to dinamicaly create the expression based on a mapping table.

Great work !!!