Adding custom rows on summarization

mohamed

Forums

Hi,
I am summarizing a file based on a column 'Category'.
Categories will be A, B, C, D, etc
So summary file will have records like given below.
Category    No of records
A                     10
B                     20
C                     15
My requirement is this:
If there are no records corresponding to Category ='B', it should show record count as zero for category B, as given below. 
Category    No of records
A                     10
B                     0
C                     15
Could you please suggest any workarounds to achieve this requirement?
Thanks in advance,
Shafeer
 
 
 

Brian Element Thu, 10/20/2016 - 11:21

Hi Shafeer,

To do what you want you will need another file with all the possible values that are possible within your summary.  So in your second case you will need the following file with these transacdtions:

A

B

C

You would then take the second file and join the first file to it so that your new file would have all the possible values and where there are no records it will show as 0.

Let me know if that makes sense.  If not I will create an example for you.

Brian

mohamed Fri, 10/21/2016 - 11:27

Thanks a lot Brian for your advice. Need help on one issue.
I could achieve the requirement by following steps.
1. Manually created a master category file with all possible category values
2. Performed a join operation of master category  file and input file (with no secondary match)
3. I will get records missing in the input file from above step.
4. Appended the missing records to the input file.
5. The appeneded database will have all categories.
One issue here is:  how can I create the master category file in run time?
I have a list of categories (say A, B, C, D etc). From this list, how can I create an IDEA file?
Thanks again
Shafeer
 

Brian Element Fri, 10/21/2016 - 12:43

Hi Shafeer,

I have a few suggestion for you to speed this up.  For creating the master category file, why don't you first summarize your current file by category and export it to Excel, at least you will have a starting point and you will only have to add the missing items.  Once that is done reimport the file into IDEA.

Instead of going through several steps use the join but selected all records in both files option.  That will add the missing items and it will show them as 0 for the number of records.

Hopefully I have answer your last two questions above, if you need further info please let me know.

Good luck.

Brian

mohamed Mon, 10/24/2016 - 03:12

Hi Brian,
<<For creating the master category file, why don't you first summarize your current file by category and export it to Excel, at least you will have a starting point and you will only have to add the missing items.  Once that is done reimport the file into IDEA>>
I did the same steps to create master category file manually. 
But how can I create this master file through idea script.
All required category items are available in my code as string values. Is there anyway to create IDEA database if these category values are stored in a list/collection or something like that?
Thanks,
Shafeer
 

Brian Element Mon, 10/24/2016 - 07:28

Hi Shafer,

Can you give me an example of how it is stored, that would give me a better idea of what to do with it.  If you could share part of the code that would be great.

Thanks

Brian

mohamed Mon, 10/24/2016 - 07:38

Hi Brian,
My master file (which has to be created in run time) will have only one column (Category char(10))
Category
-----------------------------------   
CategoryA CategoryB CategoryC
CategoryD
CategoryE
Hope this helps.
Thanks,
Shafeer
 
 

Brian Element Mon, 10/24/2016 - 07:45

Hi Shafer,

What I am trying to understand is "All required category items are available in my code as string values."  So they are all available but what does the code look like?  If there are all there there is a possbility of having the script parse through the code and create a file based on it.

Brian

mohamed Mon, 10/24/2016 - 07:56

Hi Brian,
Yes, all the category values will be avaialble in code as a list of strings or separate string variables like 
dim categoryList as string
categoryList ="CategoryA, CategoryB, CategoryC,..."
or 
categoryA= "CategoryA"
categoryB= "CategoryB"
categoryC= "CategoryC"
I can represent the category list in any standard format.
Thanks,
Shafeer

Brian Element Mon, 10/24/2016 - 08:16

How many categories do you have?  Instead of having them in the code why not in an excel spreadsheet that you can import into IDEA?  This would be a lot easier then the coding that is necessary to extract these items from your code and create an IDEA file for them.

mohamed Mon, 10/24/2016 - 08:21

Hi Brian,
Only small number of categories will be there . Maximum will be 8. 
I have to create dpack files which can be imported into IDEA Smart Analyzer. Not sure how to deliver xls files along with dpack. That's why I thought about creating master file in run time.
Thanks