0
I have an csv data dump which is a flattening of related tables from a sql database. So to simplify it looks like this.
| col1 | col2 | col3 | col4 |
+------+------+------+------+
| A | Ad | B | B1 |
| A | Ad | B | B2 |
| A | Ad | B | B3 |
| A | Ad | C | C1 |
| A | Ad | C | C2 |
| X | Xx | D | D1 |
| X | Xx | D | D2 |
| X | Xx | E | E3 |
From this table I need to generate various graphs and pivots from subsets of this data
so I would like to generate (link) to this data and create tables that represent the queryable sets I am after eg.
| col1 | col2 |
+------+------+
| A | Ad |
| X | Xx |
and
| col1 | col2 | col3 |
+------+------+------+
| A | Ad | B |
| A | Ad | C |
| X | Xx | D |
| X | Xx | E |
mostly so I can do counts of the unique combinations of the flattened data.
It is important that when I refresh the data from the datasource that these tables update accurately.
So how do I do this?
EDIT
Your answers have been helpful, it seems my question wasn't quite good enough
for the first table I really want to produce this
| col1 | col2 | count
+------+------+------
| A | Ad | 1
| X | Xx | 1
and the second one
| col1 | col2 | col3 | count
+------+------+------+------
| A | Ad | B | 1
| A | Ad | C | 1
| X | Xx | D | 1
| X | Xx | E | 1
So the counts reflect the distinct record count at a given level not the sum of all rows.
I need to answer questions like "what is the total of distinct items in col1"
I also need to answer this question "show count of col3 for col1"
I expect I will be asked to make graphs and present the data at various normalized levels too.
I hope this is more accurate for you. Thanks for the help so far
1You can use pivot table. For first result table, set col1 and col2 as row labels, set report layout to tabular form, turn off subtotals and grand total. For count of unique combinations, set values to count of column 2. Similar process for second result table, just add col3. – chuff – 2013-05-23T22:53:05.940