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