- How to convert multiple row data in single row on Advanced Reporting
- How to remove duplicates in Advanced Reporting to get single row per user
Reproducing the Issue
Query is giving multiple rows per user because of different values in a particular column
Ex: Fetching the dependent details give as many rows as there are dependents. In below example, there are 3 rows coming for a user.
This is an expected behavior as ther data is being fectched from database where is available in different columns for a user.
However, you can still remove this partial duplicacy using the "duplicate table" functionality available in Advanced Reporting.
In this, we will take example of above scenario of dependent information.
1. Intially the report is giving 3 record for a user as per above screenshot.
2. Table join will be like below:
3. To achieve a single row per user, you have to duplicate the dependent tables as many time as the number of dependents( 3 times in this example). Once the tables are duplicated, put filter on individual table to fetch one particular dependent type( spouse, child1, child2).
How to Duplicate the table:
4. Click on Edit table link of original dependent table
5. Click on "+" button on top to duplicate the table( as shown below).
6. This will add new dependent table as below:
7. To join further tables from duplicated table. Ex: Add Dependent Perfornal Info(2) linked to Dependents(2), you have to change the path of the query.
How to Change the query path:
8. Edit the table(step 4) to which you want to join new tables.
9. Click on eye button( as shown below)
10. Now if you drag any column from Dependent Perfornal Info table, it will be linked to table Dependents(2).
11. Now you have to put filter on individual tables to fecth one type of Dependent.
How to put filter on individual table:
12. Select the table (Dependent) and Edit( step 4)
13. Put filter on Relationship field to include on one type of dependent( Spouse, Child1, Child2)
14. This will give one dependent information as shown below.
15. Similarly put filer on other 2 dependent tables so that the final result is like below.
16 Finally, you can rename the columns, remove unwanted columns using "Columns" tab
17. Final Result
3 rows present initially are converted to single row.
Multiple rows in Advanced Reporting One row per user Remove duplicate rows , KBA , LOD-SF-ANA-ODS , Advanced Reporting , How To