group by in csv data set

I have a data set created from a flat file data source pointing at a csv. The data set has 4 columns: Name, Time, Group, Score 
 
I need to add a fifth computed column which is the average of the Score values where name and time are the same. So if this were sql then it would look something like: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
select name, time, avg(score)
from csv_file
group by name, time;
</pre>
 
however, because this is coming from a csv file, i can't use sql

i'm guessing this is done in the "computed columns" tab but i'm pretty stumped 
 
any ideas?

Find more posts tagged with

Comments

mwilliams

Hi AdamUK88,

You can do the grouping within a table in your report design, then use aggregations within the group footer to compute and display the averages.

Hope this helps. Let me know if you have any questions or would like a quick example posted.

AdamUK88

Thanks for the reply mwilliams, but could you expand please?

I think i see what your saying but it might be achieving something other than what i want. suppose i had some data like this:

Name | Time | group | score
NameA | 08:00 | 1 | 5
NameA | 08:00 | 2 | 8
NameA | 08:00 | 3 | 7
NameA | 09:00 | 1 | 6
NameA | 09:00 | 2 | 9
NameA | 09:00 | 3 | 6
NameB | 08:00 | 1 | 4
NameB | 08:00 | 2 | 5
NameB | 09:00 | 1 | 5
NameB | 09:00 | 2 | 5

Then the final output would look something like this, essential removing the "group" section

Name | Time | Average_score
NameA | 08:00 | 6.67
NameA | 09:00 | 7
NameB | 08:00 | 4.5
NameB | 09:00 | 5

If this is the kind of thing your method is going to result in, can i get a few directions please?

Thanks again

mwilliams

AdamUK88,

What is your BIRT version? I'll make you a quick example.

AdamUK88

I ran with what you said and found cross-tabs to be the key! <a class='bbc_url' href='http://www.birt-exchange.org/org/forum/public/style_emoticons/'>http://www.birt-exchange.org/org/forum/public/style_emoticons/</a><#EMO_DIR#>/tongue.gif 
 
Thanks again for the answer, but now i have a new query (like to keep you on your toes <a class='bbc_url' href='http://www.birt-exchange.org/org/forum/public/style_emoticons/'>http://www.birt-exchange.org/org/forum/public/style_emoticons/</a><#EMO_DIR#>/tongue.gif) 
 
i know have a table with names on the left, time across the top, and the data fields filled with average scores. Is there a way to sort which names come first in the table based on how many scores come above a certain number? 
 
e.g. if NameA had 7 average scores that were 8 or above, whereas NameB had 9 average scores that were 8 or above, NameB would be above NameA in the table. 
 
Thanks again

mwilliams

I was actually going to make a crosstab and a table grouping for you based on your data so you'd have two options when I saw what your data was going to look like! But, you beat me to it!

As for your new question, are you talking NameA Group1 or NameA as a whole?

AdamUK88

Both actually! i now need to make 2 tables both of which are sorted as mentioned above.

Scenario 1:
This would just be taking into account the average scores that have been calculated, so group has essentially been removed from the scenario now. So for each Name we have one row of results, and the names need to be sorted based on how many of those results come above 8.

Scenario 2:
This table still has groups in it. The names need to be organised by how many results come above 8 across all groups, but the names and groups need to be kept togetherand in order. e.g. if NameA has 9 scores above 8 across all the groups, whereas NameB had 12 scores above 8 all groups, NameB would be first. This needs to be shown as :

NameB - Group 1
NameB - Group 2
NameB - Group 3
NameB - Group 4
NameA - Group 1
NameA - Group 2
NameA - Group 3
NameA - Group 4

This would be the case even if, for example, NameA group 1 had more scores over 8 than NameB Group3, or NameB group 4 had more scores over 8 than NameB group 1.

Getting complex now huh? :S

mwilliams

Can you post a csv file with a chunk of sample data for me to work with? I'll try to come up with something. Also, what is your BIRT version? I'll work in your version so that you can for sure run whatever I post if I find a solution!

AdamUK88

Ok here you go. couple of things:

- The scores in this file are out of 100, but the highest one is in the 50's. 40 would be a good threshold to use in this case

- Time can be summarised to just hh:mm

eclipse platform version: 3.4.1

have fun

AdamUK88

Some additional functionality i have been looking at but haven't managed to get yet. I have a summary table at the front of the report which shows how many scores each name had every hour that were over 8. If there are no scores over 8 throughout the day, the name is omitted from the summary table

I need the other tables in the report (the ones we're already working on) to only show the names that appear in the summary table. alternatively, if it's easier to implement, this means that if there are no scores above 8 across all groups for a given name, that name is ommitted

Thanks

scaracas

You can use a Report Item ( eg your first table ) as a datasource for other items ( eg other tables in your report )

Also , you can filter all Tables based on the same rule.

Go to Filters for each Table.

Add a rule ( Score > 8 ).

AdamUK88

unfortunately that doesn't achieve what i want. There may be cases where a Name will have values over 8, but other vaues which are under 8. If i apply the filters like you suggested, the table will remove all values under 8. I only want to remove Names where there are no values over 8.

Also, because the summary table only contains a count of the data and not the data itself i can't use it as a data source.

Is there a way to apply a filter to the later tables that says "where name in summary_table"?

mwilliams

Adam,

So, you're using BIRT 2.3.1? Correct? I may have gotten confused with the time/group thing as well. Is the time what you're grouping on after "name"? Or are you grouping on "group"? Let me know.

AdamUK88

i guess it doesn't help that we have a variable called group xD the tables i have at the moment look something like this: 
 
Table 1: group has been removed, leaving name, time, and average score. This needs to be sorted by how many average scores come over 8. so if NameA has 7 average scores over 8, NameB has 9 average scores over 8, and NameC has 6 average scores over 8, it will look something like this 
 
_______| Time intervals for the columns 
NameB | 
NameA | Average scores in the cells 
NameC | 
 
Table 2: These values are lifted straight out of the data source. This needs to be sorted by Name based on how many scores are over 8, but the groups need to stay in order. e.g. if NameA had more scores over 8 across all groups than NameB, the table would look something like this. 
 
_____________| Time intervals for the columns 
NameA Group1 | 
NameA Group2 | actual scores in 
NameA Group3 | the cells 
NameA Group4 |_______________________________ 
NameB Group1 | 
NameB Group2 | actual scores 
NameB Group3 | in the cells 
NameB Group4 | 
 
The groups need to stay in order in each name, it's just the names that are sorted. 
 
 
Now the summary table shows how many scores were over 8 each hour for each name. If a name has no scores over 8 for the entire day, it is not in the table. I need to filter the other tables so that only the names which appear in the summary table are shown in the rest of the report. 
 
______|_Hour1|__Hour2__|_Hour3|... 
NameA |Number|of scores|over 8|each hour 
NameB | . . . . 
 
Yeh i think my birt version is 2.3.1

mwilliams

Unfortunately, I haven't been able to come up with a way to sort this as of yet. I can figure out the number over 40 for each section, but I can't sort a table by aggregations. Creating a hidden table that computes the values and then using that table as a dataSource may be a way. If I find a solution that meets your requirements, I'll let you know.

AdamUK88

Thanks for your continued support mwilliams, i'll keep working too

if you can find a solution to the filtering by names in the summary table thing i'd be eager to know that one. If we're placing priorities on things, i think that might come above the sorting thing . . . just

AdamUK88

ok i got it! both of em actually

i created an aggregation which was a count of scores over 8, and aggregated against the name. Then i applied a filter to the name where the aggregated value was greater than or equal to 1. then i applied a sort on the name based on the aggregated value descending.

why couldn't you apply the sort to your aggregated value? maybe my version of birt is higher than i had thought.

thanks again for all your help guys

mwilliams

AdamUK88,

I'm not sure why it wouldn't let me sort by the aggregation. It was telling me in an error that I couldn't sort by an aggregation. I'll have to look deeper into why that wouldn't work. Either way, glad you got it working! Let us know whenever you have questions!

AdamUK88

while i've got your attention i do have another issue 
 
the summary table i have at the minute shows a count of scores over 8 in each group per hour, so it looks something like this: 
 
_____________| 1 | 2 | and all the other hours 
NameA Group1 | 1 | 2 | 
NameA Group2 | 2 | 1 | and so on 
NameA Group3 | 3 | 1 | and so forth 
NameA Group4 | 4 | 2 | 
NameB Group1 | . . . 
 
as the scores are taken at 15 minute intervals the maximum count that can be shown is 4 (4 15 minutes per hour, go figure! <a class='bbc_url' href='http://www.birt-exchange.org/org/forum/public/style_emoticons/'>http://www.birt-exchange.org/org/forum/public/style_emoticons/</a><#EMO_DIR#>/tongue.gif) 
 
now i need to create another summary table but with group removed, just showing a count of 15 minute scores over 8 per hour for each name. However it needs to be a count of average scores across all groups for that name in that 15 minute interval 
 
I realise this makes as much sense as a sunbathing penguin so to summarise, the calculation process needs to be something like this: 
 
1. calculate the average score for each 15 minute interval for each name (essentially removing group from the scenario) 
2. do a count in each hour on these new average scores for how many of them are over 8 (the maximum would be 4) 
3. put these count values into the summary table 
 
The table would then look something like the one above, but without group. 
 
So far, i have managed to calculate the average scores and get a count of them per hour. however, this count is calculated using an aggregate, so i can't place it directly into the table (unless someone knows a way of doing this <a class='bbc_url' href='http://www.birt-exchange.org/org/forum/public/style_emoticons/'>http://www.birt-exchange.org/org/forum/public/style_emoticons/</a><#EMO_DIR#>/tongue.gif) 
 
Thanks again guys

AdamUK88

scratch that, i found a way to cheat

i recreated the detailed name table i'd already created, then added a column sub-total which had a count of the scores. I then hid the minute column, and filtered the whole table by average scores over 8.

Thanks again for your help

mwilliams

"I realise this makes as much sense as a sunbathing penguin so to summarise..." - ha ha ha!

Glad you found a solution. As always, let us know whenever you have questions!

Good luck!

AdamUK88

new problems arise! well . . . you could call them problems but more pedantictisms. The report now works and shows everything it should, theres just a few formatting things i want to get that i'm not having much fun with

1) I have sorted my first summary table by the count of scores above 8 throughout the day, descending. I now want to sort all the other tables in the report so that the names appear in the same order as they do in this summary table. unfortunately, applying the same technique that i used for the summary table doesn't work, as different names have varying amounts of groups, and the tables show various different timeframes.

basically, is it possible to replicate the order of names from the first summary table into other tables in the report?

2) There are 4 scores reported every hour, however the minutes shown can be somewhat messed up, and can fall a couple of minutes either side of the 15 minute interval. To fix this i have mapped numbers onto the minutes so that instead they are shown as interval 1, 2, 3 and 4. simples.

However these columns aren't grouped after being mapped. e.g. if most of the names returned a score at 59 minutes past, with the exception of one which returned a score at 58 minutes past, i had 2 columns. a 58 column which was blank for all but one name, and a 59 column which was blank on the name which returned on the 58 minute. These two values are now shown as '4' but i have been left with two '4' columns, which are blank in the same places as before.

Is there any way to group these columns based on the mapped values?

mwilliams

Is there any way you can recreate this issue in a sample report with CSV files of your data so that I can see the issue you're having? It'll be much easier to look at this issue, I think.

I think the only way you'll be able to get the sorting the same will be to do a similar sort rule on all tables. If you can reproduce your report with sample data, I'll be able to look at more things.

AdamUK88

i'm at home and not work now so i can't send examples

but i'll try explain as best i can 
 
We'll just look at the grouping thing for now, the sorting can wait. 
 
before i mapped the values the tables looked something like this, with hour and minute running along the top 
 
_____________|____1____|______2______|____3____| 
_____________|14|29|44|59|14| 29|44|58|59|14|29|44|59| 
NameA Group1| 4 | 6 | 4 | 8 | 3 | 1 | 6 | - | 2 | 9 | 3 | 6 | 3 | 
NameA Group2| 3 | 5 | 6 | 3 | 5 | 6 | 1 | - | 2 | 6 | 7 | 7 | 5 | 
NameB Group1| 5 | 7 | 7 | 2 | 2 | 6 | 3 | 4 | - | 6 | 3 | 7 | 3 | 
NameB Group2| 3 | 5 | 6 | 8 | 9 | 3 | 1 | 6 | - | 2 | 7 | 6 | 3 | 
NameC Group1| 6 | 2 | 6 | 3 | 5 | 6 | 9 | - | 2 | 6 | 3 | 5 | 2 | 
NameC Group2| 5 | 7 | 6 | 7 | 2 | 3 | 3 | - | 3 | 3 | 7 | 7 | 5 | 
 
because NameB returned scores at 2:58 rather than 2:59 like the other's, it has it's own column

i have now mapped this so it looks something like this: 
 
_____________|____1____|______2______|_____3_____| 
_____________| 1 | 2 | 3 | 4 | 1 | 2 | 3 | 4 | 4 | 1 | 2 | 3 | 4 | 
NameA Group1| 4 | 6 | 4 | 8 | 3 | 1 | 6 | - | 2 | 9 | 3 | 6 | 3 | 
NameA Group2| 3 | 5 | 6 | 3 | 5 | 6 | 1 | - | 2 | 6 | 7 | 7 | 5 | 
NameB Group1| 5 | 7 | 7 | 2 | 2 | 6 | 3 | 4 | - | 6 | 3 | 7 | 3 | 
NameB Group2| 3 | 5 | 6 | 8 | 9 | 3 | 1 | 6 | - | 2 | 7 | 6 | 3 | 
NameC Group1| 6 | 2 | 6 | 3 | 5 | 6 | 9 | - | 2 | 6 | 3 | 5 | 2 | 
NameC Group2| 5 | 7 | 6 | 7 | 2 | 3 | 3 | - | 3 | 3 | 7 | 7 | 5 | 
 
which removes any confusion of the minutes but still leaves me with blank cells. Is there a way to group the two 4's in hour 2? 
 
Thanks

mwilliams

Adam,

In your dataSet, you should be able to create a computed column to make the 58 minute times into 59 minute times....then use this field as your dimension rather than the original time. That should get rid of the blanks.

AdamUK88

That worked a treat! although i twisted ur logic a little. 
 
I created a new column like you suggested, but just used it to alter the original value, this saved me having to recreate the entire report with the new variable

here's the code if anyone is looking to do a similar thing: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
var min = row["time"].getMinutes();
if ((min>0)&&(min<=15)){min=14};
if ((min>15)&&(min<=30)){min=29};
if ((min>30)&&(min<=45)){min=44};
if ((min>45)&&(min<=59)){min=59};
if (min==0){min=59};
row["time"].setMinutes(min);
</pre>
 
one last little bit of functionality and i'll stop bugging you <a class='bbc_url' href='http://www.birt-exchange.org/org/forum/public/style_emoticons/'>http://www.birt-exchange.org/org/forum/public/style_emoticons/</a><#EMO_DIR#>/tongue.gif Is it possible to control what order filters are applied in? 
 
i have 2 filters in the detailed tables: one which filters the names by based on an aggregated count of scores over 8, while the other filters the hours down to 6 hour intervals for each table. 
 
By default the hours are filtered first, then the aggregated scores filtered afterwards. I would like this order to be reversed, so that names are filtered by the aggregated value first, then the hours are filtered afterwards. 
 
The logic behind this is that i would like names to be shown if they got a score of over 8 during the entire day, even if that score didn't land in this particular 6 hour interval. 
 
Thanks again!

Siva Subramanian

Hi, 
 
I export the csv format in birt report but aggregate values are duplicated how can i resolve this one.
 
Here i have put sample data for i'm getting
 
<div>OvcStore,0505,"",4,0.69,99.26,99.95,0.69,19,0.9633333333333334,1297.33,1309.25,11.92,19,0.9633333333333334,1297.33,1309.25,11.92</div>
<div>OvcStore,0405,"",3,1.85,201.06,204.85,3.79,19,0.9633333333333334,1297.33,1309.25,11.92,19,0.9633333333333334,1297.33,1309.25,11.92</div>
<div>OvcStore,0503,"",5,0.23,299.16,299.85,0.69,19,0.9633333333333334,1297.33,1309.25,11.92,19,0.9633333333333334,1297.33,1309.25,11.92</div>
<div>OvcStore,0403,"",2,1.18,103.66,104.9,1.24,19,0.9633333333333334,1297.33,1309.25,11.92,19,0.9633333333333334,1297.33,1309.25,11.92</div>
<div>OvcStore,0402,"",2,1.7,294.74,299.85,5.11,19,0.9633333333333334,1297.33,1309.25,11.92,19,0.9633333333333334,1297.33,1309.25,11.92</div>
<div>OvcStore,0401,"",3,0.13,299.45,299.85,0.4,19,0.9633333333333334,1297.33,1309.25,11.92,19,0.9633333333333334,1297.33,1309.25,11.92</div>