In this section: |
You are now ready to select the columns of data you want to load into the data targets. You can perform a variety of operations on the selected columns.
Right-click the SQL object and notice the options on the menu.
The Column Selection dialog box opens. You can identify column types at a glance:
Icon |
Description |
---|---|
|
Key column |
|
Regular column |
Index column | |
|
Virtual column |
Double-click the column YEARMONTH in the Available Columns list to move it into the Selected Columns list. (This is the column you created earlier as a Source Transformation.)
To open the SQL Calculator, click the Insert Columns button above the Selected Columns list.
The SQL Calculator should look like the image below:
Notice that the expression has been added at the bottom of the Selected Columns list. The expression is in the first column and the alias you assigned is in the second column.
Under Selected Columns, click T1.PROD_NUM, then select Group By from the drop-down menu in the Aggregate column.
Repeat this step for T2.STORE_CODE and for T2.YEARMONTH.
Under Selected Columns, press the Ctrl key and click T2.QUANTITY, T2.LINEPRICE, and LINECOGS. Choose Sum from the drop-down menu in the Aggregate column. Sum is applied to all three columns, as shown in the following image.
You have identified the columns you want to extract from the source data, but the data in the source goes back several years and only the last few years are really relevant to the salespeople who need to make product decisions for the coming year. Therefore, you are going to define a selection criterion to limit the retrieval to the appropriate years.
As you can see, the columns here are represented by the same icons used in the Selected Columns dialog box. Notice that YEARMONTH is a virtual column, rather than a real database column. You can filter on real or virtual columns.
Click the numbers 2 0 0 6 0 1 on the pad, or type 200601 directly into the Expression box to specify the year and month.
The expression in the WHERE Filter Calculator should look like the following image:
Tip: Though it is not required for this tutorial, you can build an SQL calculation using any ANSI SQL function. Simply click the Functions tab to see the available functions and arguments.
Note: The number of records retrieved will depend on the Run Options set from Tools and Options, and the database you choose for your sample data.
Next Step: You are ready to create the data targets into which your source data will be moved, based on the mapping and rules you defined in the SQL columns object.
iWay Software |