05-23-2012 12:56 PM
I want to automate the below result(no hardcoding).
I have three datasets namely Policy, Policy_Fact, Account_Info. Below are the columns in each table.
Col_Name of Policy_Fact
Ac_code column is mapped against the columns(except pol_num_txt) present in Policy_Fact. In the output dataset, I want only two columns pol_num_txt and Amount.
For those policies which are present in Policy table, I want to create multiple observations as per the Ac_Code present in Account_Info table.
Under the Amount column appropriate value from Policy_Fact table is expected.
Policy Policy_Fact Account_Info
pol_num_txt pol_num_txt Service_Tax Stamp_duty Sales_tax Ac_code Col_Name
101 101 12 10 8 A11 Service_Tax
102 102 12 10 8 A12 Sales_Tax
103 12 10 8
Your help is highly appreciated. Thanks.
05-24-2012 10:15 AM
How about using the following steps?
1. Create two new datasets from Policy_Fact table by using Querybuilder. The first dataset will contain two columns, pol_num_txt and Service_Tax. Assign new column name, Amount, to Service_Tax. The second dataset will contain two columns, pol_num_txt and Sales_tax. Assign new column name, Amount, to Sales_tax. So, you will have two identical datasets which have two columns, pol_num_txt and Amount. The Amount column on the first dataset actually contains data of Service_Tax, and the Amount column on the second dataset actully contains data of Sales_tax. See examples below.
First Dataset Second Dataset
pol_num_txt Amount pol_num_txt Amount
101 12 101 8
102 12 102 8
103 12 103 8
2. Open the first dataset and run Append Table. Append Table is located under Data function. Append Table enables you to concatenate the first dataset and second dataset. After completing Append Table, sort the table by pol_num_txt.