Could you please help me with the following doubt?

[neha11_ch]

Hi Everyone,

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.

Policy- Pol_num_txt

Policy_Fact- Pol_num_txt

  Service_tax

  Stamp_duty

  Sales_Tax

Account_Info- Ac_code

  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.

For eg.

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

Output Dataset

pol_num_txt    Amount

101                 12

101                  8    

102                 12

102                  8

Your help is highly appreciated. Thanks.

[ct]

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.