Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Groupping Data

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 NoDepartmentValue

201199

100.02.00110
201199100.02.00130
201199100.01.00120
201199200.02.00125
201199200.01.001200
201199300.04.000100
201200100.02.001150
201200100.02.001250
201200200.01.00290
201200200.02.00523
201200300.04.00110
201200300.03.0015

What I want:

Account NoGroup1Group2Group3
20119960225100
20120040011315

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

13 Replies
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

its_anandrjs

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

Not applicable
Author

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.

pablolabbe
Luminary Alumni
Luminary Alumni

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 !!!