Linking to find all possible values of a column

By duplicating each row in the base table, you can accommodate all matches in the foreign table when linking the two tables together on a common column.

Difficulty

Objective

You want to perform a tabulation to obtain a summary of results for different groups, but some of the groups may not have any data. However, you would like there still to be a result in the tabulation for every group. For example, you want to calculate the sum of sales per store for a specific date, but some stores might not have had sales on that particular day.

Solution

<base table="pub.doc.retail.altseg.stores"/>
<link table2="pub.doc.retail.altseg.sales_detail_transid" col="store" 
col2="store" expand="1">
  <sel value="trans_date=20151010"/>
</link>
<tabu label="Tabulation on Store Master" breaks="store,city">
  <tcol source="xsales" fun="sum" label="Sum of`Sales" format="type:currency"/>
</tabu>

Discussion

Knowing that an item contains no values for a specific date can be useful. However, if you perform a tabulation and no values exist for a certain field, that field will simply not be present in the tabulation. If you instead link in a table that contains all possible values for that field and specify that expand="1", performing the same tabulation will create a row for that field.

Instead of tabulating on the Sales Detail table, this solution uses the Store Master table as a base and links in the Sales Detail table. Within the <link>, you can use a selection to only obtain transactions from October 10, 2010. Even though no transactions were made in store 186 on that date, a row is still created due to the expand attribute.

Expand="1" will duplicate every row in the Store Master table to accommodate every match in the Sales Detail table. Since there are no matches for store 186 in the Sales Detail table, it will not be expanded, but that row will still be kept in the final table and it will show that there was no sales. After the tabulation, the Sum of Sales tabulation shows that store 186 had a total of $0.00 sales.

Further reading

If you would like to learn more about the functions and operations discussed in this recipe, click on the links below:

<link>

<tabu>