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: